Create a date from day month and year with T-SQL

Question

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.

Oxime

Please consider changing your accepted answer weblogs.sqlteam.com/jeffs/archive/2007/09/10/… – Galliwasp

DATEFROMPARTS(year, month, day) – Toxoplasmosis

Answer 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

Kerril

Bad one. Compose me from ints the date of 1st Jan 0001 – Traction

Oleg SQL Server DateTime don't go further back then 1753-01-01 something. – Disproportionate

This answer depends on the date format settings, which are dependent on the regional settings of your server if you don't specify. The yyyymmdd format works regardless of those settings. "A six- or eight-digit string is always interpreted as ymd." learn.microsoft.com/en-us/sql/t-sql/data-types/… See this answer: https://mcmap.net/q/110149/-how-to-create-a-date-in-sql-server-given-the-day-month-and-year-as-integers – Pyelitis

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

Mendy

@Brandon, you should mark this as this answer instead. It's the best one. Do it as a service to other StackOverflow readers. – Meticulous

Works for leap years: select dateadd(mm,(@y-1900)* 12 + @m - 1,0) + (@d-1) – Nadya

Results in a valid yet spurious date value when passed invalid combination of values e.g. @Year = 2001, @Month = 13 and @DayOfMonth = 32 results in 2002-02-01T00:00:00.000. The accepted answer (by Cade Roux) generates an error, which is more useful. – Barograph

You don't have to start with zero and add days. You can start directly with @DayOfMonth-1, then add the months and years. That's one less DateAdd()! – Elena

my head's still spinning - there really isn't a neater way to do this? (I am tasked to fix a query in SQL Server 2005) – Suburbanize

@Peter, what do you mean 'neater'? it seems to me that Select DateAdd(yy, @Year-1900, DateAdd(m, @Month - 1, @DayOfMonth - 1)) is pretty neat and concise. – Mendy

Be careful if you initialize your variables using the DAY() MONTH() YEAR() DATEPART shorthand syntax. They return INT and have to be CAST to TINYINT. This is one of those cost of conversion or size of variable hair splitters. – Agulhas

@BillPaetzke I prefer the DateAdd() variant too, as it is region-independent. Just be aware that you can enter INVALID dates -- e.g. an input of (2015, 2, 31) is accepted, and it is converted to "March 3, 2015". – Miscellany

It should be noted that this doesn't appear to work for leap years: e.g., 2008-02-29: SELECT DATEADD(yy, 2008 - 1900, DATEADD(mm, 2 - 1, 29 - 1)); – Numb

Correction: The original proposal appears to work for leap years too. The solution proposed by @Elena doesn't. – Numb

Down-voted until the solution removes or notes the leap year bug in the middle solution... – Numb

@Numb Interesting, it's because 1900 isn't a leap year. Changing the order of the addition fixes it: select dateadd(month, 2 - 1, dateadd(year, 2008 - 1900, 29 - 1)); → 2008-02-29. – Elena

@CharlesBretana is this a social experiment to see how many wrong answers can be upvoted to be the best answer? – Anticlimax

The best answer now appears to be the DATEFROMPARTS from @Cade Roux – Lustig

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

Kerril

Furthermore, refering to original question, where Datetime object was mentioned, there is also a function called DATETIMEFROMPARTS: msdn.microsoft.com/pl-pl/library/hh213233%28v=sql.110%29.aspx – Stalwart

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

Rainout

best answer IMO. Has all the advantages of Charles' answer, and is much shorter. – Bernal

This is by far the cleanest and simplest. And it does not throw an error when day values are out of range either. Altho depending on the circumstance, an error may be desired, so just be aware that this silences day and month values that are out of expected range. – Territorialism

Answer 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

Abutilon

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

Peria

Agree the format needs to be qualified, e.g. CONVERT(datetime2, CAST(@year AS varchar) + '.' + CAST(@month AS varchar) + '.' + CAST(@day AS varchar), 102) – Domestic

Answer 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

Blare

Answer 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

Sliwa

Why don't use just declare @output datetime2 = 0 and instead of @Year - 1900 use @Year - DATEPART(year,0);? This works without any casts in SQL Server 2008 and much more clear. – Roseanneroseate

Because that won't work. You can not cast 0 to datetime2. Your code will return "Operand type clash: int is incompatible with datetime2" – Sliwa

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

Hypermetropia

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

Hypochondria

Answer 11

Try

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

Courlan

Answer 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

Nogood

Answer 13

I know the OP is asking for SQL 2005 answer but the question is pretty old so if you're running SQL 2012 or above you can use the following:

SELECT DATEADD(DAY, 1, EOMONTH(@somedate, -1))

Reference: https://learn.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql?view=sql-server-2017&viewFallbackFrom=sql-server-previousversions

Oller

Answer 14

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

Dampier

Answer 15

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

Potpourri