如何在 SQL Server 2008 R2 中将系统日期格式转换为 dd/mm/yy？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15017300/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert the system date format to dd/mm/yy in SQL Server 2008 R2?
提问by Adalarasan_Serangulam
I'm using SQL Server 2008 R2. I want to convert the system date to this format: dd/mm/yy
我正在使用 SQL Server 2008 R2。我想将系统日期转换为这种格式：dd/mm/yy
"2013-01-01 00:00:00.000" to "Score Calculation - 10/01/13".
My column contains the data:
我的列包含数据：
1. DMS01A13010101
2. RMS01A13010201
3. 44
4. 2013-01-01 00:00:00.000
What I want: if the record has 2013-01-01 00:00:00.000in this format then only I change to Score Caculation - dd/mm/yy
我想要的是：如果记录2013-01-01 00:00:00.000采用这种格式，那么只有我更改为Score Caculation - dd/mm/yy
My code is,
我的代码是，
select
case
when (CHARINDEX(D30.SPGD30_TRACKED_ADJUSTMENT_X, '-*') > 0 or
CHARINDEX(D30.SPGD30_TRACKED_ADJUSTMENT_X, '*-') > 0)
then 'Score Calculation - ' + CONVERT(VARCHAR(8), D30.SPGD30_TRACKED_ADJUSTMENT_X, 1)
end checkthedate
from
CSPGD30_TRACKING D30
回答by Mudassir Hasan
SELECT CONVERT(varchar(11),getdate(),101) -- mm/dd/yyyy
SELECT CONVERT(varchar(11),getdate(),103) -- dd/mm/yyyy
Check this . I am assuming D30.SPGD30_TRACKED_ADJUSTMENT_X is of datetime datatype .
That is why i am using CAST()function to make it as an character expression because CHARINDEX()works on character expression.
Also I think there is no need of OR condition.
检查这个。我假设 D30.SPGD30_TRACKED_ADJUSTMENT_X 是 datetime 数据类型。
这就是为什么我使用CAST()函数将其作为字符表达式的原因，因为它CHARINDEX()适用于字符表达式。
另外我认为不需要 OR 条件。
select case when CHARINDEX('-',cast(D30.SPGD30_TRACKED_ADJUSTMENT_X as varchar )) > 0
then 'Score Calculation - '+CONVERT(VARCHAR(11), D30.SPGD30_TRACKED_ADJUSTMENT_X, 103)
end
EDIT:
编辑：
select case when CHARINDEX('-',D30.SPGD30_TRACKED_ADJUSTMENT_X) > 0
then 'Score Calculation - '+
CONVERT( VARCHAR(11), CAST(D30.SPGD30_TRACKED_ADJUSTMENT_X as DATETIME) , 103)
end
See this link for conversion to other date formats: https://www.w3schools.com/sql/func_sqlserver_convert.asp
请参阅此链接以转换为其他日期格式：https: //www.w3schools.com/sql/func_sqlserver_convert.asp
回答by John Woo
The query below will result in dd/mm/yyformat.
下面的查询将导致dd/mm/yy格式。
select LEFT(convert(varchar(10), @date, 103),6) + Right(Year(@date)+ 1,2)
回答by Er Ketan Vavadiya
Try this
尝试这个
SELECT CONVERT(varchar(11),getdate(),101) -- Converts to 'mm/dd/yyyy'
SELECT CONVERT(varchar(11),getdate(),103) -- Converts to 'dd/mm/yyyy'
More info here: https://msdn.microsoft.com/en-us/library/ms187928.aspx
更多信息：https: //msdn.microsoft.com/en-us/library/ms187928.aspx
回答by Palash
select convert(varchar(8), getdate(), 3)
simply use this for dd/mm/yy and this
只需将此用于 dd/mm/yy 和此
select convert(varchar(8), getdate(), 1)
for mm/dd/yy
月/日/年
回答by Dipin C.K
The query below will result in dd-mmm-yy format.
下面的查询将产生 dd-mmm-yy 格式。
select
cast(DAY(getdate()) as varchar)+'-'+left(DATEname(m,getdate()),3)+'-'+
Right(Year(getdate()),2)
