如何在 SQL Server 2008 R2 中将系统日期格式转换为 dd/mm/yy? [英] How to convert the system date format to dd/mm/yy in SQL Server 2008 R2?
问题描述
我使用的是 SQL Server 2008 R2.我想将系统日期转换成这种格式:dd/mm/yy
I'm using SQL Server 2008 R2. I want to convert the system date to this format: dd/mm/yy
"2013-01-01 00:00:00.000" to "Score Calculation - 10/01/13".
我的列包含数据:
1. DMS01A13010101
2. RMS01A13010201
3. 44
4. 2013-01-01 00:00:00.000
我想要的是:如果记录有 2013-01-01 00:00:00.000
这种格式,那么我只更改为 Score Caculation - dd/mm/yy
What I want: if the record has 2013-01-01 00:00:00.000
in this format then only I change to Score Caculation - dd/mm/yy
我的代码是,
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
推荐答案
SELECT CONVERT(varchar(11),getdate(),101) -- mm/dd/yyyy
SELECT CONVERT(varchar(11),getdate(),103) -- dd/mm/yyyy
检查这个.我假设 D30.SPGD30_TRACKED_ADJUSTMENT_X 是日期时间数据类型.
这就是为什么我使用 CAST()
函数将其作为字符表达式的原因,因为 CHARINDEX()
适用于字符表达式.
另外我认为不需要 OR 条件.
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.
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
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
请参阅此链接以转换为其他日期格式:https://www.w3schools.com/sql/func_sqlserver_convert.asp
See this link for conversion to other date formats: https://www.w3schools.com/sql/func_sqlserver_convert.asp
这篇关于如何在 SQL Server 2008 R2 中将系统日期格式转换为 dd/mm/yy?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!