用T-SQL创建一个日期。

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中的datetime。我试过以下几点:

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.

但这导致了错误的日期。将三个日期值转换为合适的日期时间格式的正确方法是什么?

14 个解决方案

#1

160

Assuming y, m, d are 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 answer for SQL Server 2012 and above

请参见我对SQL Server 2012及以上版本的回答

#2

319

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价值观是价值两部分的第一部分是一个整数代表的天数自1900年1月1日起,第二部分是十进制分数，表示一天的小数部分(暂时是)——所以整数值0(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(年、月、日)，它做同样的事情。

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));

#3

216

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

DATEFROMPARTS(ycolumn, mcolumn, dcolumn)

或

DATEFROMPARTS(@y, @m, @d)

#4

115

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)

#5

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

#6

Try CONVERT instead of CAST.

尝试转换而不是铸造。

CONVERT allows a third parameter indicating the date format.

转换允许第三个参数指示日期格式。

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设置的答案，而没有指出这个限制。

#7

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

在SQL中工作。2012年,AzureSQL

#8

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

#9

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')))

#10

I add a one-line solution if you need a datetime from both date and time parts:

如果您需要一个日期和时间部分的datetime，我添加一个单行解决方案:

select dateadd(month, (@Year -1900)*12 + @Month -1, @DayOfMonth -1) + dateadd(ss, @Hour*3600 + @Minute*60 + @Second, 0) + dateadd(ms, @Millisecond, 0)

#11

Try

试一试

CAST(STR(DATEPART(year, DATE))+'-'+ STR(DATEPART(month, DATE)) +'-'+ STR(DATEPART(day, DATE)) AS DATETIME)

#12

For SQL Server versions below 12 i can recommend use of CAST in combination with SET DATEFORMAT

对于低于12的SQL Server版本，我建议使用CAST和SET DATEFORMAT

-- 26 February 2015
SET DATEFORMAT dmy
SELECT CAST('26-2-2015' AS DATE)

SET DATEFORMAT ymd
SELECT CAST('2015-2-26' AS DATE)

how you create those strings is up to you

如何创建这些字符串取决于您

#13

Try this query:

试试这个查询:

    SELECT SUBSTRING(CONVERT(VARCHAR,JOINGDATE,103),7,4)AS
    YEAR,SUBSTRING(CONVERT(VARCHAR,JOINGDATE,100),1,2)AS
MONTH,SUBSTRING(CONVERT(VARCHAR,JOINGDATE,100),4,3)AS DATE FROM EMPLOYEE1

Result:

结果:

2014    Ja    1
2015    Ja    1
2014    Ja    1
2015    Ja    1
2012    Ja    1
2010    Ja    1
2015    Ja    1

#14

I personally Prefer Substring as it provide cleansing options and ability to split the string as needed. The assumption is that the data is of the format 'dd, mm, yyyy'.

我个人更喜欢Substring，因为它提供了清理选项和根据需要拆分字符串的能力。假设数据是“dd, mm, yyyy”格式。

--2012 and above
SELECT CONCAT (
        RIGHT(REPLACE(@date, ' ', ''), 4)
        ,'-'
        ,RIGHT(CONCAT('00',SUBSTRING(REPLACE(@date, ' ', ''), CHARINDEX(',', REPLACE(@date, ' ', '')) + 1, LEN(REPLACE(@date, ' ', '')) - CHARINDEX(',', REPLACE(@date, ' ', '')) - 5)),2)
        ,'-'
        ,RIGHT(CONCAT('00',SUBSTRING(REPLACE(@date, ' ', ''), 1, CHARINDEX(',', REPLACE(@date, ' ', '')) - 1)),2)
        )

--2008 and below
SELECT   RIGHT(REPLACE(@date, ' ', ''), 4)
        +'-'
        +RIGHT('00'+SUBSTRING(REPLACE(@date, ' ', ''), CHARINDEX(',', REPLACE(@date, ' ', '')) + 1, LEN(REPLACE(@date, ' ', '')) - CHARINDEX(',', REPLACE(@date, ' ', '')) - 5),2)
        +'-'
        +RIGHT('00'+SUBSTRING(REPLACE(@date, ' ', ''), 1, CHARINDEX(',', REPLACE(@date, ' ', '')) - 1),2)

Here is a demonstration of how it can be sued if the data is stored in a column. Needless to say, its ideal to check the result-set before applying to the column

这里演示了如果数据存储在一个列中，它是如何被起诉的。不用说，在应用到列之前，最好先检查结果集

DECLARE @Table TABLE (ID INT IDENTITY(1000,1), DateString VARCHAR(50), DateColumn DATE)

INSERT INTO @Table
SELECT'12, 1, 2007',NULL
UNION
SELECT'15,3, 2007',NULL
UNION
SELECT'18, 11 , 2007',NULL
UNION
SELECT'22 , 11, 2007',NULL
UNION
SELECT'30, 12, 2007  ',NULL

UPDATE @Table
SET DateColumn = CONCAT (
        RIGHT(REPLACE(DateString, ' ', ''), 4)
        ,'-'
        ,RIGHT(CONCAT('00',SUBSTRING(REPLACE(DateString, ' ', ''), CHARINDEX(',', REPLACE(DateString, ' ', '')) + 1, LEN(REPLACE(DateString, ' ', '')) - CHARINDEX(',', REPLACE(DateString, ' ', '')) - 5)),2)
        ,'-'
        ,RIGHT(CONCAT('00',SUBSTRING(REPLACE(DateString, ' ', ''), 1, CHARINDEX(',', REPLACE(DateString, ' ', '')) - 1)),2)
        ) 

SELECT ID,DateString,DateColumn
FROM @Table

#1

160