如何从 SQL (T-SQL) 中的日期组件创建日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5772866/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create dates from date components in SQL (T-SQL)?
提问by Kenny Evitt
How can I construct native date data type values in SQL (T-SQL)?
如何在 SQL (T-SQL) 中构造本机日期数据类型值？
I've added some examples, but please provide your own. My examples assume that the month and year are being stored (or are readily available) as integer values, but maybe yourexample will assume that the day and the month (or whatever) are stored as text. I can't see the future; surprise me.
我已经添加了一些示例，但请提供您自己的示例。我的示例假设月份和年份被存储（或随时可用）为整数值，但也许您的示例将假设日期和月份（或其他）存储为文本。我看不到未来；让我吃惊。
采纳答案by Andriy M
Why, with input data as strings one of the most obvious (and therefore hardly surprising, sorry) solutions would be:
为什么，将输入数据作为字符串，最明显（因此并不奇怪，抱歉）的解决方案之一是：
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;
回答by Martin Smith
回答by Kenny Evitt
Date values from year, month, AND day (integer) values, though maybe the inputs should be sanitized first:
来自年、月和日（整数）值的日期值，但也许应该首先清理输入：
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;
回答by Kenny Evitt
The following code shows how to create date values from year and month (integer) values:
以下代码显示了如何从年和月（整数）值创建日期值：
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;
回答by Kenny Evitt
More example code to create date values from year and month (integer) values, but even simpler than some other example code:
从年和月（整数）值创建日期值的更多示例代码，但比其他一些示例代码更简单：
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;
