如何在SQL查询中生成日期？ [英] How to generate date in SQL Query?

问题描述

我想从2011年1月1日开始-2011年12月31日在SQL查询中生成日期值。

I want to generate date value since 1/1/2011 - 31/12/2011 on sql query.

示例
我的表A

Example My Table A

Field A     Field B       Field C
   1       01/01/2011      125
   2       03/01/2011      100
   3       05/01/2011       50 

我想得出结果：

Field A     Field B       Field C
   1       01/01/2011      125
   0       02/01/2011        0
   2       03/01/2011      100
   0       04/01/2011        0
   3       05/01/2011       50 
   ...
   0       31/12/2011       0 

请给我建议，非常感谢。

Please Advice Me and Thank a lot.

推荐答案

在SQL Server中，您可以创建一个表值函数而不是创建一个临时表，因为这可以在查询之间重用：

Under SQL Server, you can create a table-valued function rather than creating a temporary table, as this is reusable between queries:

-- List all of the dates between startdate and enddate (inclusive)
CREATE FUNCTION [dbo].[DatesBetween] (
    @startdate date,
    @enddate date
) RETURNS @ret TABLE (Date date) AS BEGIN

    DECLARE @dt date, @dtEnd date
    SELECT @dt = @startdate, @dtEnd = @enddate

    WHILE (@dt <= @dtEnd) BEGIN
        INSERT INTO @ret VALUES(@dt)
        SET @dt = DATEADD(day, 1, @dt)
    END 

    RETURN
END

这使得所有内容都可以在一个查询中执行：

This allows everything to be executed inside one query:

SELECT d.Date, COUNT(t.*) AS TotalOnDay
FROM dbo.DatesBetween('2011-01-01', '2011-12-31') d 
    LEFT JOIN MyTable t ON t.Date = d.Date
GROUP BY d.Date

这篇关于如何在SQL查询中生成日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！