如何在SQL（T-SQL）中从日期组件创建日期？

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

如何在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.

我添加了一些示例,但请提供您自己的示例。我的示例假设月份和年份以整数值存储(或随时可用),但也许您的示例将假设日期和月份(或其他)存储为文本。我看不到未来;让我感到惊讶

5 个解决方案

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

(来自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;

#1