Geek asks and answers

Create a Result Set of Calendar Dates

I have to use a stored procedure to populate a calendar for 10 years in an SQL database!

Three columns must fill in the entries: date, day of the week or weekend, name of the day (Monday, ...).

Can someone help me?

+3
source share
4 answers

You can do this dynamically as follows:

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)
+2
source

try this version (fills in the date and time of adding all requested columns):

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

to insert into a table, try the following:

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)

select top 10 * from @DateTable

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)
0
source

. spt_values ​​ Master . 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
0

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

Output

1/1/2011 Monday 7 Week_day 1/2/2011 Tuesday 1 week_day

0
source

More articles:


All Articles