创建日历日期的结果集

问题描述：

我应该使用存储过程在SQL数据库中填充日历10年！创建日历日期的结果集

我应该填写三栏：日期，工作日或周末，日期（星期一，...）。

有人可以帮我吗？

确定您需要记录？也许与..不存在..会给你你想要的结果。 – Teson 2011-05-16 18:48:37

答

您可以动态地做到这一点是这样的：

WITH Dates AS (
     SELECT CONVERT(DATE, getdate()) as [Date] 
     UNION ALL 
     SELECT DATEADD(DAY, 1, [Date]) 
     FROM Dates 
     where Date < dateadd(yy, 10, getdate()) 

) 
SELECT [Date] 
FROM Dates 
OPTION (MAXRECURSION 4000)

我几乎可以确定你的意思是'CONVERT（DATE，getdate（））'。 – 2011-05-16 22:25:53

@Andriy M是的，对于SQL Server 2008来说这更好 – RedFilter 2011-05-17 15:22:32

答

试试这个版本（地板的日期时间，并增加了所有需要的列）：

;WITH Dates AS (
     SELECT DATEADD(day,DATEDIFF(day,0,GETDATE()),0) as DateOf, 
      CASE WHEN datename(weekday,getdate()) IN ('Saturday','Sunday') THEN 'Weekend' 
       ELSE 'WeekDay' 
      END DayType, 
      datename(weekday,getdate()) DayOfWeekName 
     UNION ALL 
     SELECT DateOf+1, 
      CASE WHEN datename(weekday,DateOf+1) IN ('Saturday','Sunday') THEN 'Weekend' 
       ELSE 'WeekDay' 
      END DayType, 
      datename(weekday,DateOf+1) DayOfWeekName 
     FROM Dates 
     where DateOf < dateadd(yy, 10, getdate()) 

) 
SELECT DateOf,DayType,DayOfWeekName 
FROM Dates 
OPTION (MAXRECURSION 4000)

插入到表试试这个：

DECLARE @DateTable table (DateOf datetime, DayType char(7), DayOfWeekName varchar(10)) 

;WITH Dates AS (
     SELECT DATEADD(day,DATEDIFF(day,0,GETDATE()),0) as DateOf, 
      CASE WHEN datename(weekday,getdate()) IN ('Saturday','Sunday') THEN 'Weekend' 
       ELSE 'WeekDay' 
      END DayType, 
      datename(weekday,getdate()) DayOfWeekName 
     UNION ALL 
     SELECT DateOf+1, 
      CASE WHEN datename(weekday,DateOf+1) IN ('Saturday','Sunday') THEN 'Weekend' 
       ELSE 'WeekDay' 
      END DayType, 
      datename(weekday,DateOf+1) DayOfWeekName 
     FROM Dates 
     where DateOf < dateadd(yy, 10, getdate()) 

) 
INSERT INTO @DateTable (DateOf,DayType,DayOfWeekName) 
    SELECT DateOf,DayType,DayOfWeekName 
    FROM Dates 
    OPTION (MAXRECURSION 4000)

从@DateTable中选择排名前10位*

OTUPUT：

DateOf     DayType DayOfWeekName 
----------------------- ------- ------------- 
2011-05-16 00:00:00.000 WeekDay Monday 
2011-05-17 00:00:00.000 WeekDay Tuesday 
2011-05-18 00:00:00.000 WeekDay Wednesday 
2011-05-19 00:00:00.000 WeekDay Thursday 
2011-05-20 00:00:00.000 WeekDay Friday 
2011-05-21 00:00:00.000 Weekend Saturday 
2011-05-22 00:00:00.000 Weekend Sunday 
2011-05-23 00:00:00.000 WeekDay Monday 
2011-05-24 00:00:00.000 WeekDay Tuesday 
2011-05-25 00:00:00.000 WeekDay Wednesday 

(10 row(s) affected)

我喜欢这些解决方案，但我需要使用startdate作为参数！ – Rachid 2011-05-17 08:27:53

@Rachid，很简单，只需要用你的参数替换所有'GETDATE（）'：'@StartDate datetime' – RacerX 2011-05-17 19:38:24

谢谢赛车手，它真的很有帮助!!!!! – Rachid 2011-05-18 07:26:46

答

我apporached这是一个符合表问题。我使用Master的spt_values作为我的理货表。它只能达到2048年，这是足够5.5年的数据。您可以根据需要创建自己的理货表。

Declare @startDate Date = '1/1/2011'; 

SELECT DateAdd(d, number, @startDate) [Date], 
     CASE WHEN DATEPART(dw, DateAdd(d, number, @startDate)) IN (1,7) THEN 'Weekend' ELSE 'Weekday' END [WeekDayEnd], 
     DateName(weekday, DateAdd(d, number, @startDate)) DayOfWeek 
FROM spt_values 
WHERE type = 'P';

这得到以下结果：

Date  WeekDayEnd DayOfWeek 
2011-01-01 Weekend  Saturday 
2011-01-02 Weekend  Sunday 
2011-01-03 Weekday  Monday 
2011-01-04 Weekday  Tuesday 
2011-01-05 Weekday  Wednesday 
2011-01-06 Weekday  Thursday 
2011-01-07 Weekday  Friday 
2011-01-08 Weekend  Saturday 
2011-01-09 Weekend  Sunday 
2011-01-10 Weekday  Monday

答

使用本

set nocount on 

SET DATEFIRST 7; 

go 
select date, 
datename(dw,datepart(dw,date)) Day, 
datepart(dw,date) Day, 
'Segment' = case 
when datepart(dw,date)in (5,6) then 'WEEK_END' else 'Week_day' end 
from calenderdate 

set nocount off

> set nocount on 
> 
> select date, 
> substring(cast(datename(dw,datepart(dw,date))as 
> varchar(10)),1,3) Day, 'Segment' = 
> case when datepart(dw,date)in (5,6) 
> then 'WEEK_END' else 'Week_day' end 
> from calenderdate 
> 
> set nocount off

输出

1/1/2011星期一7 Week_day 1/2/2011星期一1 Week_day

创建日历日期的结果集

相关推荐