将整数转换为日期和时间格式?
问题描述:
在SQL Server中,如何将一个整数转换的日期和时间,例如:将整数转换为日期和时间格式?
declare @constant int =92011 or 102011
如何将其转换成类似“九月-2011”或“10 - 2011”日期格式。
答
试试这个:
declare @c int
set @c = 92011
SELECT DATENAME(MONTH,CAST(RIGHT(@c,4) + '-' + LEFT(@C, LEN(@C)-4) + '-01' AS DATE)) + '-' + RIGHT(@c,4)
或 以下情况:
declare @c int
set @c = 92011
select CASE WHEN LEFT(@C, LEN(@C)-4) = 1 THEN 'January'
WHEN LEFT(@C, LEN(@C)-4) = 2 THEN 'February'
WHEN LEFT(@C, LEN(@C)-4) = 3 THEN 'March'
WHEN LEFT(@C, LEN(@C)-4) = 4 THEN 'April'
WHEN LEFT(@C, LEN(@C)-4) = 5 THEN 'May'
WHEN LEFT(@C, LEN(@C)-4) = 6 THEN 'June'
WHEN LEFT(@C, LEN(@C)-4) = 7 THEN 'July'
WHEN LEFT(@C, LEN(@C)-4) = 8 THEN 'August'
WHEN LEFT(@C, LEN(@C)-4) = 09 THEN 'September'
WHEN LEFT(@C, LEN(@C)-4) = 10 THEN 'October'
WHEN LEFT(@C, LEN(@C)-4) = 11 THEN 'November'
WHEN LEFT(@C, LEN(@C)-4) = 12 THEN 'December'
ELSE '' END + '-' + RIGHT(@c,4)
答
declare @c int
set @c = 102011
;with M(Num, Name) as
(
select 1, 'January' union all
select 2, 'February' union all
select 3, 'March' union all
select 4, 'April' union all
select 5, 'May' union all
select 6, 'June' union all
select 7, 'Juli' union all
select 8, 'August' union all
select 9, 'September' union all
select 10, 'October' union all
select 11, 'Novmber' union all
select 12, 'December'
)
select M.Name+'-'+right(@c, 4)
from M
where M.Num = left(@c, len(@c)-4)
在回答使用DATENAME由Upendra Chaudhari提供启发。
select datename(month, dateadd(month, left(@c, len(@c)-4) - 1, 0))+'-'+right(@c, 4)
在第一个代码,如果你会使用数字,如92011.叟,你应该使用这样的代码,你会得到错误: 'SELECT DATENAME(MONTH,CAST(RIGHT(@ c,4)+' - '+ LEFT(@ c,LEN(@c)-4)+'-01'AS DATE))+' - '+ RIGHT(@ c,4)' – Dalex
@Dalex:是的,检查我的更新答案。 –