给定日、月和年作为整数，如何在 SQL Server 中创建日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35576983/
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 a Date in SQL Server given the Day, Month and Year as Integers
提问by MswatiLomnyama
FOR Example if I have:
例如，如果我有：
DECLARE @Day int = 25
DECLARE @Month int = 10
DECLARE @Year int = 2016
I want to return
我想回来
2016-10-25
As Date or datetime
作为日期或日期时间
回答by Gordon Linoff
In SQL Server 2012+, you can use datefromparts():
在 SQL Server 2012+ 中，您可以使用datefromparts()：
select datefromparts(@year, @month, @day)
In earlier versions, you can cast a string. Here is one method:
在早期版本中，您可以转换字符串。这是一种方法：
select cast(cast(@year*10000 + @month*100 + @day as varchar(255)) as date)
回答by Riley Major
In SQL Server 2012+, you can use DATEFROMPARTS():
在 SQL Server 2012+ 中，您可以使用DATEFROMPARTS()：
DECLARE @Year int = 2016, @Month int = 10, @Day int = 25;
SELECT DATEFROMPARTS (@Year, @Month, @Day);
In earlier versions, one method is to create and convert a string.
在早期版本中，一种方法是创建和转换字符串。
There are a few string date formats which SQL Server reliably interprets regardless of the date, language, or internationalization settings.
无论日期、语言或国际化设置如何，SQL Server 都能可靠地解释一些字符串日期格式。
A six- or eight-digit string is always interpreted as ymd. The month and day must always be two digits.
六位或八位字符串始终被解释为 ymd。月和日必须始终为两位数。
https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql
https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql
So a string in the format 'yyyymmdd' will always be properly interpreted.
因此，格式为 'yyyymmdd' 的字符串将始终被正确解释。
(ISO 8601-- YYYY-MM-DDThh:mm:ss-- also works, but you have to specify time and therefore it's more complicated than you need.)
（ISO 8601 YYYY-MM-DDThh:mm:ss--- 也适用，但您必须指定时间，因此它比您需要的更复杂。）
While you can simply CASTthis string as a date, you must use CONVERTin order to specify a style, and you must specify a style in order to be deterministic(if that matters to you).
虽然您可以简单地CAST将此字符串作为日期，但您必须使用CONVERT它来指定样式，并且必须指定样式才能确定（如果这对您很重要）。
The "yyyymmdd" format is style 112, so your conversion looks like this:
“yyyymmdd”格式为样式 112，因此您的转换如下所示：
DECLARE @Year int = 2016, @Month int = 10, @Day int = 25;
SELECT CONVERT(date,CONVERT(varchar(50),(@Year*10000 + @Month*100 + @Day)),112);
And it results in:
结果是：
2016-10-25
2016-10-25
Technically, the ISO/112/yyyymmdd format works even with other styles specified. For example, using that text format with style 104 (German, dd.mm.yyyy):
从技术上讲，ISO/112/yyyymmdd 格式甚至适用于指定的其他样式。例如，使用样式为 104（德语，dd.mm.yyyy）的文本格式：
DECLARE @Year int = 2016, @Month int = 10, @Day int = 25;
SELECT CONVERT(date,CONVERT(varchar(50),(@Year*10000 + @Month*100 + @Day)),104);
Also still results in:
也仍然导致：
2016-10-25
2016-10-25
Other formats are not as robust. For example this:
其他格式不那么健壮。例如这个：
SELECT CASE WHEN CONVERT(date,'01-02-1900',110) = CONVERT(date,'01-02-1900',105) THEN 1 ELSE 0 END;
Results in:
结果是：
0
0
As a side note, with this method, beware that nonsense inputs can yield valid but incorrect dates:
作为旁注，使用此方法，请注意无意义的输入可能会产生有效但不正确的日期：
DECLARE @Year int = 2016, @Month int = 0, @Day int = 1025;
SELECT CONVERT(date,CONVERT(varchar(50),(@Year*10000 + @Month*100 + @Day)),112);
Also yields:
还产生：
2016-10-25
2016-10-25
DATEFROMPARTSprotects you from invalid inputs. This:
DATEFROMPARTS保护您免受无效输入的侵害。这个：
DECLARE @Year int = 2016, @Month int = 10, @Day int = 32;
SELECT DATEFROMPARTS (@Year, @Month, @Day);
Yields:
产量：
Msg 289, Level 16, State 1, Line 2 Cannot construct data type date, some of the arguments have values which are not valid.
消息 289，级别 16，状态 1，第 2 行无法构造数据类型日期，某些参数的值无效。
Also beware that this method does not work for dates prior to 1000-01-01. For example:
另请注意，此方法不适用于 1000-01-01 之前的日期。例如：
DECLARE @Year int = 900, @Month int = 1, @Day int = 1;
SELECT CONVERT(date,CONVERT(varchar(50),(@Year*10000 + @Month*100 + @Day)),112);
Yields:
产量：
Msg 241, Level 16, State 1, Line 2 Conversion failed when converting date and/or time from character string.
消息 241，级别 16，状态 1，第 2 行 从字符串转换日期和/或时间时转换失败。
That's because the resulting string, '9000101', is not in the 'yyyymmdd' format. To ensure proper formatting, you'd have to pad it with leading zeroes, at the sacrifice of some small amount of performance. For example:
这是因为生成的字符串“9000101”不是“yyyymmdd”格式。为了确保格式正确，您必须用前导零填充它，但会牺牲一些性能。例如：
DECLARE @Year int = 900, @Month int = 1, @Day int = 1;
SELECT CONVERT(date,RIGHT('000' + CONVERT(varchar(50),(@Year*10000 + @Month*100 + @Day)),8),112);
Results in:
结果是：
0900-01-01
0900-01-01
There are other methods aside from string conversion. Several are provided in answers to "Create a date with T-SQL". A notable exampleinvolves creating the date by adding years, months, and days to the "zero date".
除了字符串转换之外，还有其他方法。“使用 T-SQL 创建日期”的答案中提供了几个。一个值得注意的示例涉及通过将年、月和日添加到“零日期”来创建日期。
(This answer was inspired by Gordon Linoff'sanswer, which I expanded on and provided additional documentation and notes.)
（这个答案的灵感来自Gordon Linoff 的答案，我对其进行了扩展并提供了额外的文档和注释。）
回答by Shubham Pandey
The following code should work on all versions of sql server I believe:
以下代码应该适用于我相信的所有版本的 sql server：
SELECT CAST(CONCAT(CAST(@Year AS VARCHAR(4)), '-',CAST(@Month AS VARCHAR(2)), '-',CAST(@Day AS VARCHAR(2))) AS DATE)
回答by Milan Hettner
Old Microsoft Sql Sever (< 2012)
旧的 Microsoft Sql Sever (< 2012)
RETURN dateadd(month, 12 * @year + @month - 22801, @day - 1)
回答by Muriel Souza
So, you can try this solution:
所以，你可以试试这个解决方案：
DECLARE @DAY INT = 25
DECLARE @MONTH INT = 10
DECLARE @YEAR INT = 2016
DECLARE @DATE AS DATETIME
SET @DATE = CAST(RTRIM(@YEAR * 10000 + @MONTH * 100 + @DAY) AS DATETIME)
SELECT REPLACE(CONVERT(VARCHAR(10), @DATE, 102), '.', '-') AS EXPECTDATE
Or you can try this a few lines of code:
或者你可以试试这几行代码：
DECLARE @DAY INT = 25
DECLARE @MONTH INT = 10
DECLARE @YEAR INT = 2016
SELECT CAST(RTRIM(@YEAR * 10000 +'-' + @MONTH * 100+ '-' + @DAY) AS DATE) AS EXPECTDATE
回答by Hasan Junaid Hashmi
select convert(varchar(11), transfer_date, 106)
选择转换（varchar（11），transfer_date，106）
got me my desired result of date formatted as 07 Mar 2018
得到了我想要的日期格式为 2018 年 3 月 7 日的结果
My column 'transfer_date' is a datetime type column and I am using SQL Server 2017 on azure
我的“transfer_date”列是日期时间类型列，我在 azure 上使用 SQL Server 2017
回答by JIYAUL MUSTAPHA
CREATE DATE USING MONTH YEAR IN SQL::
在 SQL 中使用月年创建日期::
DECLARE @FromMonth int=NULL,
@ToMonth int=NULL,
@FromYear int=NULL,
@ToYear int=NULL
/**Region For Create Date**/
DECLARE @FromDate DATE=NULL
DECLARE @ToDate DATE=NULL
SET @FromDate=DateAdd(day,0, DateAdd(month, @FromMonth - 1,DateAdd(Year, @FromYear-1900, 0)))
SET @ToDate=DateAdd(day,-1, DateAdd(month, @ToMonth - 0,DateAdd(Year, @ToYear-1900, 0)))
/**Region For Create Date**/
