如何在SQL(T-SQL)中构建本地日期数据类型值?
我已经添加了一些示例,但请提供您自己的示例。我的示例假设月份和年份作为整数值存储(或容易获得),但也许您的示例将假定日期和月份(或其他内容)以文本形式存储。我无法预测未来,请给我惊喜。
SELECT DATEFROMPARTS(@Year, @Month, @Day)
(来自SQL Server 2012)
,所以我认为这很清楚... - Martin Smith(2009,13,1)
或(2009,1,32)
这样的值,你会得到一个异常。你需要传入有效日期的值。如果你对代码有疑问,请提出一个新问题。 - Martin Smith如果输入数据是字符串,最显而易见的解决方案之一是:
SELECT
mydate = CAST([year] + RIGHT('0' + [month], 2) + '01' AS datetime)
/* or 'AS date' in SQL Server 2008+ */
FROM (
SELECT [month] = '2', [year] = '2011' UNION ALL
SELECT [month] = '03', [year] = '2011' UNION ALL
SELECT [month] = '5', [year] = '2011' UNION ALL
SELECT [month] = '12', [year] = '2011' UNION ALL
SELECT [month] = '8', [year] = '2084' UNION ALL
SELECT [month] = '1', [year] = '1940'
) x;
SELECT DATEADD(
month,
DATEDIFF( month, 0, GETDATE() )
+ x.[month]
- MONTH( GETDATE() ),
DATEADD(
year,
DATEDIFF( year, 0, GETDATE() )
+ x.[year]
- YEAR( GETDATE() ),
0 ) )
FROM ( SELECT [month] = 2, [year] = 2011
UNION ALL
SELECT [month] = 3, [year] = 2011
) x;
日期值由年、月和日(整数)值组成,不过输入值可能需要先进行清理:
SELECT DATEADD(
day,
x.[day] - DAY(0),
DATEADD(
month,
x.[month] - MONTH(0),
DATEADD(
year,
x.[year] - YEAR(0),
0 ) ) )
FROM ( SELECT [month] = 2, [year] = 2011, [day] = 14
UNION ALL
SELECT [month] = 3, [year] = 2011, [day] = 2
UNION ALL
SELECT [month] = 5, [year] = 2011, [day] = 1
UNION ALL
SELECT [month] = 7, [year] = 2011, [day] = 0
UNION ALL
SELECT [month] = 8, [year] = 2084, [day] = 40
UNION ALL
SELECT [month] = 1, [year] = 1940, [day] = -6
) x;
更多示例代码可从年份和月份(整数)值创建日期值,但比其他示例代码更简单:
SELECT DATEADD(
month,
x.[month] - MONTH(0),
DATEADD(
year,
x.[year] - YEAR(0),
0 ) )
FROM ( SELECT [month] = 2, [year] = 2011
UNION ALL
SELECT [month] = 3, [year] = 2011
UNION ALL
SELECT [month] = 5, [year] = 2011
UNION ALL
SELECT [month] = 7, [year] = 2011
UNION ALL
SELECT [month] = 8, [year] = 2084
UNION ALL
SELECT [month] = 1, [year] = 1940
) x;