How to create dates from date components in SQL (T

问题:

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.

回答1:

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;

回答2:

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

(From SQL Server 2012)

回答3:

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;

回答4:

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;

回答5:

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;