使用 T-SQL 从年月日创建日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/266924/
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
Create a date from day month and year with T-SQL
提问by Brandon
I am trying to convert a date with individual parts such as 12, 1, 2007 into a datetime in SQL Server 2005. I have tried the following:
我正在尝试将包含 12, 1, 2007 等单个部分的日期转换为 SQL Server 2005 中的日期时间。我尝试了以下操作：
CAST(DATEPART(year, DATE)+'-'+ DATEPART(month, DATE) +'-'+ DATEPART(day, DATE) AS DATETIME)
but this results in the wrong date. What is the correct way to turn the three date values into a proper datetime format.
但这会导致错误的日期。将三个日期值转换为正确的日期时间格式的正确方法是什么。
采纳答案by Cade Roux
Assuming y, m, dare all int, how about:
假设y, m, d都是int，怎么样：
CAST(CAST(y AS varchar) + '-' + CAST(m AS varchar) + '-' + CAST(d AS varchar) AS DATETIME)
Please see my other answerfor SQL Server 2012 and above
回答by Charles Bretana
Try this:
尝试这个：
Declare @DayOfMonth TinyInt Set @DayOfMonth = 13
Declare @Month TinyInt Set @Month = 6
Declare @Year Integer Set @Year = 2006
-- ------------------------------------
Select DateAdd(day, @DayOfMonth - 1,
DateAdd(month, @Month - 1,
DateAdd(Year, @Year-1900, 0)))
It works as well, has added benefit of not doing any string conversions, so it's pure arithmetic processing (very fast) and it's not dependent on any date format This capitalizes on the fact that SQL Server's internal representation for datetime and smalldatetime values is a two part value the first part of which is an integer representing the number of days since 1 Jan 1900, and the second part is a decimal fraction representing the fractional portion of one day (for the time) --- So the integer value 0 (zero) always translates directly into Midnight morning of 1 Jan 1900...
它也能正常工作，增加了不进行任何字符串转换的好处，因此它是纯算术处理（非常快）并且不依赖于任何日期格式这充分利用了 SQL Server 对 datetime 和 smalldatetime 值的内部表示是两个的事实part value 第一部分是一个整数，表示自 1900 年 1 月 1 日以来的天数，第二部分是一个小数，表示一天的小数部分（时间）--- 所以整数值 0（零) 总是直接转换为 1900 年 1 月 1 日午夜...
or, thanks to suggestion from @brinary,
或者，感谢@brinary 的建议，
Select DateAdd(yy, @Year-1900,
DateAdd(m, @Month - 1, @DayOfMonth - 1))
Edited October 2014. As Noted by @cade Roux, SQL 2012 now has a built-in function:DATEFROMPARTS(year, month, day)
that does the same thing.
2014 年 10 月编辑。正如@cade Roux 所指出的，SQL 2012 现在有一个内置函数：DATEFROMPARTS(year, month, day)
它做同样的事情。
Edited 3 Oct 2016, (Thanks to @bambams for noticing this, and @brinary for fixing it), The last solution, proposed by @brinary. does not appear to work for leap years unless years addition is performed first
2016 年 10 月 3 日编辑，（感谢 @bambams 注意到这一点，并感谢 @brinary 修复它），最后一个解决方案，由 @brinary 提出。除非首先执行年份加法，否则似乎对闰年不起作用
select dateadd(month, @Month - 1,
dateadd(year, @Year-1900, @DayOfMonth - 1));
回答by Cade Roux
SQL Server 2012 has a wonderful and long-awaited new DATEFROMPARTS function (which will raise an error if the date is invalid - my main objection to a DATEADD-based solution to this problem):
SQL Server 2012 有一个美妙且期待已久的新 DATEFROMPARTS 函数（如果日期无效，它将引发错误 - 我主要反对基于 DATEADD 解决此问题的方法）：
http://msdn.microsoft.com/en-us/library/hh213228.aspx
http://msdn.microsoft.com/en-us/library/hh213228.aspx
DATEFROMPARTS(ycolumn, mcolumn, dcolumn)
or
或者
DATEFROMPARTS(@y, @m, @d)
回答by Shrike
Or using just a single dateadd function:
或者只使用一个 dateadd 函数：
DECLARE @day int, @month int, @year int
SELECT @day = 4, @month = 3, @year = 2011
SELECT dateadd(mm, (@year - 1900) * 12 + @month - 1 , @day - 1)
回答by Brian
Sql Server 2012 has a function that will create the date based on the parts (DATEFROMPARTS). For the rest of us, here is a db function I created that will determine the date from the parts (thanks @Charles)...
Sql Server 2012 有一个函数可以根据部分（DATEFROMPARTS）创建日期。对于我们其他人，这是我创建的一个 db 函数，它将确定零件的日期（感谢@Charles）...
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[func_DateFromParts]'))
DROP FUNCTION [dbo].[func_DateFromParts]
GO
CREATE FUNCTION [dbo].[func_DateFromParts]
(
@Year INT,
@Month INT,
@DayOfMonth INT,
@Hour INT = 0, -- based on 24 hour clock (add 12 for PM :)
@Min INT = 0,
@Sec INT = 0
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(second, @Sec,
DATEADD(minute, @Min,
DATEADD(hour, @Hour,
DATEADD(day, @DayOfMonth - 1,
DATEADD(month, @Month - 1,
DATEADD(Year, @Year-1900, 0))))))
END
GO
You can call it like this...
你可以这样称呼它...
SELECT dbo.func_DateFromParts(2013, 10, 4, 15, 50, DEFAULT)
Returns...
返回...
2013-10-04 15:50:00.000
回答by devio
Try CONVERT instead of CAST.
尝试 CONVERT 而不是 CAST。
CONVERT allows a third parameter indicating the date format.
CONVERT 允许使用第三个参数来指示日期格式。
List of formats is here: http://msdn.microsoft.com/en-us/library/ms187928.aspx
格式列表在这里：http: //msdn.microsoft.com/en-us/library/ms187928.aspx
Update after another answer has been selected as the "correct" answer:
在另一个答案被选为“正确”答案后更新：
I don't really understand why an answer is selected that clearly depends on the NLS settings on your server, without indicating this restriction.
我真的不明白为什么选择的答案显然取决于您服务器上的 NLS 设置，而没有指明此限制。
回答by Marcelo Lujan
You can also use
你也可以使用
select DATEFROMPARTS(year, month, day) as ColDate, Col2, Col3
From MyTable Where DATEFROMPARTS(year, month, day) Between @DateIni and @DateEnd
Works in SQL since ver.2012 and AzureSQL
自 ver.2012 和 AzureSQL 开始在 SQL 中工作
回答by Hyman
It is safer and neater to use an explicit starting point '19000101'
使用明确的起点“19000101”更安全、更整洁
create function dbo.fnDateTime2FromParts(@Year int, @Month int, @Day int, @Hour int, @Minute int, @Second int, @Nanosecond int)
returns datetime2
as
begin
-- Note! SQL Server 2012 includes datetime2fromparts() function
declare @output datetime2 = '19000101'
set @output = dateadd(year , @Year - 1900 , @output)
set @output = dateadd(month , @Month - 1 , @output)
set @output = dateadd(day , @Day - 1 , @output)
set @output = dateadd(hour , @Hour , @output)
set @output = dateadd(minute , @Minute , @output)
set @output = dateadd(second , @Second , @output)
set @output = dateadd(ns , @Nanosecond , @output)
return @output
end
回答by Robert Wagner
If you don't want to keep strings out of it, this works as well (Put it into a function):
如果您不想将字符串排除在外，这也可以（将其放入函数中）：
DECLARE @Day int, @Month int, @Year int
SELECT @Day = 1, @Month = 2, @Year = 2008
SELECT DateAdd(dd, @Day-1, DateAdd(mm, @Month -1, DateAdd(yy, @Year - 2000, '20000101')))
回答by bluish
I add a one-line solution if you need a datetime from both date and time parts:
如果您需要日期和时间部分的日期时间，我会添加一个单行解决方案：
select dateadd(month, (@Year -1900)*12 + @Month -1, @DayOfMonth -1) + dateadd(ss, @Hour*3600 + @Minute*60 + @Second, 0) + dateadd(ms, @Millisecond, 0)
