How to create dates from date components in SQL (T-SQL)?

Question

How can I construct native date data type values in SQL (T-SQL)?

I've added some examples, but please provide your own. My examples assume that the month and year are being stored (or are readily available) as integer values, but maybe your example will assume that the day and the month (or whatever) are stored as text. I can't see the future; surprise me.

There are other questions that are really the same as this, but in the interests of providing as many search results for different *wordings* of what are really the same question, I created this question (instead of, as I initially started to, editing the title of another question). — Kenny Evitt, Apr 24 '11 at 20:02

Martin Smith · Answer 1 · 2015-02-15T22:08:08.270

20

SELECT DATEFROMPARTS(@Year, @Month, @Day)

(From SQL Server 2012)

edited Feb 15 '15 at 22:08

answered Jul 21 '11 at 00:13

Martin Smith

438,706
87
741
845

@Augustas - The answer only contains two lines. One of them states `(From SQL Server 2012)` so I would have thought that is clear... – Martin Smith Mar 03 '17 at 15:09
OK but you might get exception for 29/02. I am using DATEADD(DAY, @ Day - 1,DATEFROMPARTS(@ Year, @ month, 1)) – Čikić Nenad Apr 22 '17 at 18:31
@ČikićNenad yes you would get an exception if you passed in `(2009,13,1)` or `(2009,1,32)` as well. You need to pass in values that make a valid date. If you have a question about some issue with your code ask a new question. – Martin Smith Apr 22 '17 at 18:35

score 6 · Accepted Answer · answered Apr 24 '11 at 20:56

6

Why, with input data as strings one of the most obvious (and therefore hardly surprising, sorry) solutions would be:

SELECT
  mydate = CAST([year] + RIGHT('0' + [month], 2) + '01' AS datetime)
                                           /* or 'AS date' in SQL Server 2008+ */
FROM (
  SELECT [month] = '2',  [year] = '2011' UNION ALL
  SELECT [month] = '03', [year] = '2011' UNION ALL
  SELECT [month] = '5',  [year] = '2011' UNION ALL
  SELECT [month] = '12', [year] = '2011' UNION ALL
  SELECT [month] = '8',  [year] = '2084' UNION ALL
  SELECT [month] = '1',  [year] = '1940'
) x;

answered Apr 24 '11 at 20:56

Andriy M

76,112
17
94
154

This is just a caching strategy for programming algorithms (i.e. programmers)! – Kenny Evitt Apr 25 '11 at 00:49
1

+1 The point being that T-SQL will always convert strings in the form 'yyyymmdd' correctly... no matter what you're using as a custom date format. (Assuming of course the string is a valid date.) – Disillusioned Apr 25 '11 at 14:20
In the interests of 'accepting an answer' for all of my questions, I've chosen this as 'the winner'. Congratulations! – Kenny Evitt Jul 18 '12 at 16:19

score 1 · Answer 3 · answered Apr 24 '11 at 19:55

The following code shows how to create date values from year and month (integer) values:

SELECT  DATEADD(
            month,
            DATEDIFF( month, 0, GETDATE() )
                + x.[month]
                - MONTH( GETDATE() ),
            DATEADD(
                year,
                DATEDIFF( year, 0, GETDATE() )
                    + x.[year]
                    - YEAR( GETDATE() ),
                0 ) )
FROM (  SELECT [month] = 2, [year] = 2011
        UNION ALL
        SELECT [month] = 3, [year] = 2011
    ) x;

score 1 · Answer 4 · answered Apr 25 '11 at 00:48

Date values from year, month, AND day (integer) values, though maybe the inputs should be sanitized first:

SELECT  DATEADD(
            day,
            x.[day] - DAY(0),
            DATEADD(
                month,
                x.[month] - MONTH(0),
                DATEADD(
                    year,
                    x.[year] - YEAR(0),
                    0 ) ) )
FROM (  SELECT [month] = 2, [year] = 2011, [day] = 14
        UNION ALL
        SELECT [month] = 3, [year] = 2011, [day] = 2
        UNION ALL
        SELECT [month] = 5, [year] = 2011, [day] = 1
        UNION ALL
        SELECT [month] = 7, [year] = 2011, [day] = 0
        UNION ALL
        SELECT [month] = 8, [year] = 2084, [day] = 40
        UNION ALL
        SELECT [month] = 1, [year] = 1940, [day] = -6
    ) x;

score 0 · Answer 5 · answered Apr 24 '11 at 20:01

More example code to create date values from year and month (integer) values, but even simpler than some other example code:

SELECT  DATEADD(
            month,
            x.[month] - MONTH(0),
            DATEADD(
                year,
                x.[year] - YEAR(0),
                0 ) )
FROM (  SELECT [month] = 2, [year] = 2011
        UNION ALL
        SELECT [month] = 3, [year] = 2011
        UNION ALL
        SELECT [month] = 5, [year] = 2011
        UNION ALL
        SELECT [month] = 7, [year] = 2011
        UNION ALL
        SELECT [month] = 8, [year] = 2084
        UNION ALL
        SELECT [month] = 1, [year] = 1940
    ) x;

How to create dates from date components in SQL (T-SQL)?

5 Answers5

Linked