Create a date from day month and year with 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:

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.

回答1:

Assuming y, m, d are all int, how about:

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

回答2:

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...

or, thanks to suggestion from @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.

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

select dateadd(month, @Month - 1, 
     dateadd(year, @Year-1900, @DayOfMonth - 1)); 

回答3:

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

http://msdn.microsoft.com/en-us/library/hh213228.aspx

DATEFROMPARTS(ycolumn, mcolumn, dcolumn)

or

DATEFROMPARTS(@y, @m, @d)

回答4:

Or using just a single dateadd function:

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

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

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.

回答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

回答8:

It is safer and neater to use an explicit starting point \'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:

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

-- 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\'.

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