想要在sql或ms访问中显示从'1/1/2000'到'1/12/2020'的日期 [英] want to display the dates starting from '1/1/2000' to '1/12/2020' in sql or ms access

问题描述

i希望在sql中显示从''1/1/2000''到''1/12/2020''的所有日期或ms访问。

Hi,

i want to display all the dates starting from ''1/1/2000'' to ''1/12/2020'' in sql or ms access.

推荐答案

DECLARE @startDate DATETIME
    DECLARE @endDate DATETIME

    SET @startDate = '2013-01-01'
    SET @endDate = '2013-01-31';

    WITH dates(Date) AS 
    (
        SELECT @startdate as Date
        UNION ALL
        SELECT DATEADD(d,1,[Date])
        FROM dates 
        WHERE DATE < @enddate
    )

    SELECT Date
    FROM dates
    OPTION (MAXRECURSION 0 )
    GO

你可以使用SQL的 DATEADD 函数。



请参阅以下链接，了解有关DATEADD的更多信息。

DATEADD-MSDN [ ^ ]

SQL Server DATEADD（）函数 [ ^ ]



所以，试试如下。

Hi,

you can make use of DATEADD function of SQL.

refer below links for more on DATEADD.
DATEADD-MSDN[^]
SQL Server DATEADD() Function[^]

So, try like below.

DECLARE @StartDate DATETIME, @EndDate DATETIME
CREATE TABLE @Dates (DateCol DATETIME)

SET @StartDate = '1/1/2000'
SET @EndDate = '1/12/2020'

INSERT INTO @Dates Values(@StartDate)

WHILE @StartDate < @EndDate
BEGIN
    SET @StartDate = DATEADD(day,1,@StartDate)
    INSERT INTO @Dates Values(@StartDate)
END
INSERT INTO @Dates Values(@EndDate)

SELECT DateCol FROM @Dates

希望它有所帮助。

hope it helps.

这篇关于想要在sql或ms访问中显示从'1/1/2000'到'1/12/2020'的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！