在 SQL Server 中将一年中的某一天转换为日期时间

2022-01-03 00:00:00 datetime tsql sql-server sql-server-2012

在数据库表 (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));

LiveDemo

相关文章