在SQL Server中将一年中的日期转换为日期时间
问题描述:
在数据库表(SQL Server 2012)中,我以 yyyyxxx
格式保存了日期,其中 xxx
是一年中的一天。例如 2015-08-11
是 2015223
。
In a database table (SQL Server 2012), I have dates saved in the yyyyxxx
format, where xxx
is the day of the year. For example 2015-08-11
is 2015223
.
如何将这种格式有效地转换为 DATETIME
?如何将 DATETIME
转换为这种格式?
How can I efficiently convert this format to DATETIME
? How about converting DATETIME
to this format?
答
获取年份部分,转换为日期时间+添加日期
Get the Year part, convert to datetime + add days
DECLARE @var NVARCHAR(100) = '2015223';
SELECT CAST(LEFT(@var, 4) AS DATETIME) + CAST(RIGHT(@var, 3) AS INT) - 1;
或:
DECLARE @var NVARCHAR(100) = '2015223';
SELECT DATEADD(dd,CAST(RIGHT(@var, 3) AS INT)-1, CAST(LEFT(@var, 4) AS DATETIME))
或者yyyyxxx是INT:
Or if yyyyxxx is INT:
DECLARE @var INT = 2015223;
SELECT DATEADD(dd,(@var%1000)-1, CAST(CAST((@var/1000) AS NVARCHAR(100))
AS DATETIME));