Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»

Date Calendar Expand / Collapse
Author
Message
Posted Monday, September 9, 2013 7:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:04 AM
Points: 280, Visits: 1,110
CELKO (7/21/2010)
4. Fixed date but slides to Monday if Saturday or Tuesday if Sunday (UK Boxing Day is the only one).


2nd Jan should also be in Scotland
Post #1492763
Posted Monday, September 9, 2013 7:52 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 12, 2014 1:59 PM
Points: 155, Visits: 66
Date tables are amazing! I can't tell you how many times I've found this simple little thing to be a huge time saver. Just being able to bang your data against a pre-determined date set can be the difference in getting a report back in a few seconds to a few hours.

My calendar table also had holidays in it (I needed those for certain charges to apply). I remember the first time I built the table and told my colleague about it, he thought I was crazy and had just wasted my time.

The first time I showed him how to easily generate a report that spanned a given week however, he changed his tune.

Thanks for this article, it included a lot of things I never included but could see being used.


Ad maiorem Dei gloriam
Post #1492792
Posted Monday, September 9, 2013 8:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:42 PM
Points: 389, Visits: 761
Thanks for the kind words. :) Funny thing is I too had a similar situation with one colleague. Changed his tune too once he saw the power behind it. :)
Post #1492797
Posted Monday, September 9, 2013 11:25 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 12:16 PM
Points: 22, Visits: 124
Thanks much. I imagine most people have written some version of this with the columns they most need, but a single script collecting them all is great. I've got my own with a handful of our holidays and some other stuff.

Because this script is a one-time run and doesn't need to be efficient, those wanting is_businessday can do what I do and just add it at the end, calculating it based on is_weekday and a to-be-added holiday column... which would probably require customization for most places. Celko posted awesome resources, my own I'm-too-lazy-this-is-good-enough code for some standard U.S. holidays was was:

[Holiday] AS
CASE
WHEN MONTH(date)=1 AND DAY(date)=1 THEN 'New Years Day'
WHEN MONTH(date)=5 AND DatePart(weekday, DateAdd(day,@@DATEFIRST,[date]))=2 AND DAY(date) BETWEEN 25 AND 31 THEN 'Memorial Day'
WHEN MONTH(date)=7 AND DAY(date)=4 THEN 'Independence Day'
WHEN MONTH(date)=9 AND DatePart(weekday, DateAdd(day,@@DATEFIRST,[date]))=2 AND DAY(date) BETWEEN 1 AND 7 THEN 'Labor Day'
WHEN MONTH(date)=11 AND DatePart(weekday, DateAdd(day,@@DATEFIRST,[date]))=5 AND DAY(date) BETWEEN 22 and 28 THEN 'Thanksgiving'
WHEN MONTH(date)=12 AND DAY(date)=25 THEN 'Christmas Eve'
WHEN MONTH(date)=12 AND DAY(date)=26 THEN 'Christmas Day'
ELSE '' -- or NULL depending on your preference
END /* CASE */,

Post #1492881
Posted Monday, September 9, 2013 11:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:42 PM
Points: 389, Visits: 761
Awesome! Thanks for sharing. :)
Post #1492887
Posted Tuesday, September 10, 2013 5:14 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Great script!

 
Post #1493397
Posted Tuesday, September 10, 2013 5:44 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:42 PM
Points: 389, Visits: 761
Thank you!
Post #1493399
Posted Tuesday, September 10, 2013 9:07 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Sean

I haven't had a chance to review the entire script in detail nor have I gone back to read all the comments so if the following issue has already been discussed, please point it out.

Did you include an option to display the cardinality of the days, i.e., '2nd of March', '3rd of March', '4th of March', etc.

Thanks

 
Post #1493428
Posted Wednesday, September 11, 2013 9:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:42 PM
Points: 389, Visits: 761
No, not yet. Very few changes in a long time actually. SSC just decided to re-feature it despite nothing really new in the script.
Post #1493752
Posted Wednesday, September 11, 2013 8:21 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
OK...here ya go...includes Brian's suggestion for Holidays. I turned it into a stored procedure:

    
CREATE PROCEDURE dbo.UTIL_CreateDateCalendar

@Date_Start DATETIME
,@Date_End DATETIME

AS
BEGIN

/*
EXEC dbo.UTIL_CreateDateCalendar '20100101','20201231'
*/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ARITHIGNORE ON
SET TEXTSIZE 2147483647

----------------------------------------------------------------------------------------------------------------------
-- Permanent Table: Create Date Xref Table
----------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'dbo.UTIL_DateCalendar', N'U') IS NOT NULL
DROP TABLE dbo.UTIL_DateCalendar

CREATE TABLE dbo.UTIL_DateCalendar
(
calendar_date DATETIME
NOT NULL
CONSTRAINT PK_UTIL_DateCalendar_calendar_date
PRIMARY KEY CLUSTERED
,calendar_year INT NULL
,calendar_month INT NULL
,calendar_day INT NULL
,calendar_quarter INT NULL
,first_day_in_week DATETIME NULL
,last_day_in_week DATETIME NULL
,is_week_in_same_month INT NULL
,first_day_in_month DATETIME NULL
,last_day_in_month DATETIME NULL
,is_last_day_in_month INT NULL
,first_day_in_quarter DATETIME NULL
,last_day_in_quarter DATETIME NULL
,is_last_day_in_quarter INT NULL
,day_of_week INT NULL
,week_of_month INT NULL
,week_of_quarter INT NULL
,week_of_year INT NULL
,days_in_month INT NULL
,month_days_remaining INT NULL
,weekdays_in_month INT NULL
,month_weekdays_remaining INT NULL
,month_weekdays_completed INT NULL
,days_in_quarter INT NULL
,quarter_days_remaining INT NULL
,quarter_days_completed INT NULL
,weekdays_in_quarter INT NULL
,quarter_weekdays_remaining INT NULL
,quarter_weekdays_completed INT NULL
,day_of_year INT NULL
,year_days_remaining INT NULL
,is_weekday INT NULL
,is_leap_year INT NULL
,day_name VARCHAR(10) NULL
,month_day_name_instance INT NULL
,quarter_day_name_instance INT NULL
,year_day_name_instance INT NULL
,month_name VARCHAR(10) NULL
,year_week CHAR(6) NULL
,year_month CHAR(6) NULL
,year_quarter CHAR(6) NULL
);


----------------------------------------------------------------------------------------------------------------------
-- Table Insert: Populate Base Date Values Into Permanent Table Using Common Table Expression (CTE)
----------------------------------------------------------------------------------------------------------------------

WITH cte_date_base_table
AS (
SELECT
@Date_Start AS calendar_date
UNION ALL
SELECT
DATEADD(DAY, 1, CTE.calendar_date)
FROM
cte_date_base_table CTE
WHERE
DATEADD(DAY, 1, CTE.calendar_date)<=@Date_End
)
INSERT INTO dbo.UTIL_DateCalendar
(
calendar_date
)
SELECT
CTE.calendar_date
FROM
cte_date_base_table CTE
OPTION
(MAXRECURSION 0)


----------------------------------------------------------------------------------------------------------------------
-- Table Update I: Populate Additional Date Xref Table Fields (Pass I)
----------------------------------------------------------------------------------------------------------------------

UPDATE
dbo.UTIL_DateCalendar
SET
calendar_year=DATEPART(YEAR, calendar_date)
,calendar_month=DATEPART(MONTH, calendar_date)
,calendar_day=DATEPART(DAY, calendar_date)
,calendar_quarter=DATEPART(QUARTER, calendar_date)
,first_day_in_week=
DATEADD(DAY, -DATEPART(WEEKDAY, calendar_date)+1,calendar_date)
,first_day_in_month=CONVERT (VARCHAR(6), calendar_date, 112) +'01'
,day_of_week=DATEPART(WEEKDAY, calendar_date)
,week_of_year=DATEPART(WEEK, calendar_date)
,day_of_year=DATEPART(DAYOFYEAR, calendar_date)
,is_weekday=
ISNULL((
CASE
WHEN ((@@DATEFIRST-1) +(DATEPART(WEEKDAY, calendar_date) -1))%7 NOT IN (5, 6)
THEN 1
END), 0)
,day_name=DATENAME(WEEKDAY, calendar_date)
,month_name=DATENAME(MONTH, calendar_date)


ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN calendar_year INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN calendar_month INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN calendar_day INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN calendar_quarter INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN first_day_in_week DATETIME NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN first_day_in_month DATETIME NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN day_of_week INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN week_of_year INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN day_of_year INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN is_weekday INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN day_name VARCHAR (10) NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN month_name VARCHAR (10) NOT NULL

CREATE NONCLUSTERED INDEX IX_UTIL_DateCalendar_calendar_year ON dbo.UTIL_DateCalendar (calendar_year)
CREATE NONCLUSTERED INDEX IX_UTIL_DateCalendar_calendar_month ON dbo.UTIL_DateCalendar (calendar_month)
CREATE NONCLUSTERED INDEX IX_UTIL_DateCalendar_calendar_quarter ON dbo.UTIL_DateCalendar (calendar_quarter)
CREATE NONCLUSTERED INDEX IX_UTIL_DateCalendar_first_day_in_week ON dbo.UTIL_DateCalendar (first_day_in_week)
CREATE NONCLUSTERED INDEX IX_UTIL_DateCalendar_day_of_week ON dbo.UTIL_DateCalendar (day_of_week)
CREATE NONCLUSTERED INDEX IX_UTIL_DateCalendar_is_weekday ON dbo.UTIL_DateCalendar (is_weekday)


----------------------------------------------------------------------------------------------------------------------
-- Table Update II: Populate Additional Date Xref Table Fields (Pass II)
----------------------------------------------------------------------------------------------------------------------

UPDATE
dbo.UTIL_DateCalendar
SET
last_day_in_week=first_day_in_week+6
,last_day_in_month=DATEADD(MONTH, 1, first_day_in_month)-1
,first_day_in_quarter=A.first_day_in_quarter
,last_day_in_quarter=A.last_day_in_quarter
,week_of_month=DATEDIFF(WEEK, first_day_in_month, calendar_date)
+1
,week_of_quarter=(week_of_year-A.min_week_of_year_in_quarter)+1
,is_leap_year=
ISNULL((
CASE
WHEN calendar_year%400=0 THEN 1
WHEN calendar_year%100=0 THEN 0
WHEN calendar_year%4=0 THEN 1
END), 0)
,year_week=
CONVERT (VARCHAR(4), calendar_year)
+RIGHT('0'+CONVERT (VARCHAR(2), week_of_year),2)
,year_month=
CONVERT (VARCHAR(4), calendar_year)
+RIGHT('0'+CONVERT (VARCHAR(2), calendar_month),2)
,year_quarter=
CONVERT (VARCHAR(4), calendar_year)+'Q'
+CONVERT (VARCHAR(1), calendar_quarter)
FROM
(
SELECT
X.calendar_year AS subquery_calendar_year
,X.calendar_quarter AS subquery_calendar_quarter
,MIN(X.calendar_date) AS first_day_in_quarter
,MAX(X.calendar_date) AS last_day_in_quarter
,MIN(X.week_of_year) AS min_week_of_year_in_quarter
FROM
dbo.UTIL_DateCalendar X
GROUP BY
X.calendar_year
,X.calendar_quarter
) A
WHERE
A.subquery_calendar_year=calendar_year
AND A.subquery_calendar_quarter=calendar_quarter


ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN last_day_in_week DATETIME NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN last_day_in_month DATETIME NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN first_day_in_quarter DATETIME NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN last_day_in_quarter DATETIME NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN week_of_month INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN week_of_quarter INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN is_leap_year INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN year_week VARCHAR (6) NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN year_month VARCHAR (6) NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN year_quarter VARCHAR (6) NOT NULL

CREATE NONCLUSTERED INDEX IX_UTIL_DateCalendar_last_day_in_week ON dbo.UTIL_DateCalendar (last_day_in_week)
CREATE NONCLUSTERED INDEX IX_UTIL_DateCalendar_year_month ON dbo.UTIL_DateCalendar (year_month)
CREATE NONCLUSTERED INDEX IX_UTIL_DateCalendar_year_quarter ON dbo.UTIL_DateCalendar (year_quarter)


----------------------------------------------------------------------------------------------------------------------
-- Table Update III: Populate Additional Date Xref Table Fields (Pass III)
----------------------------------------------------------------------------------------------------------------------

UPDATE
dbo.UTIL_DateCalendar
SET
is_last_day_in_month=
(CASE
WHEN last_day_in_month=calendar_date THEN 1
ELSE 0
END)
,is_last_day_in_quarter=
(CASE
WHEN last_day_in_quarter=calendar_date THEN 1
ELSE 0
END)
,days_in_month=DATEPART(DAY, last_day_in_month)
,weekdays_in_month=A.weekdays_in_month
,days_in_quarter=DATEDIFF(DAY, first_day_in_quarter,last_day_in_quarter)+1
,quarter_days_remaining=DATEDIFF(DAY, calendar_date,last_day_in_quarter)
,weekdays_in_quarter=B.weekdays_in_quarter
,year_days_remaining=(365+is_leap_year)-day_of_year

FROM
(
SELECT
X.year_month AS subquery_year_month
,SUM(X.is_weekday) AS weekdays_in_month
FROM
dbo.UTIL_DateCalendar X
GROUP BY
X.year_month
) A
,(
SELECT
X.year_quarter AS subquery_year_quarter
,SUM(X.is_weekday) AS weekdays_in_quarter
FROM
dbo.UTIL_DateCalendar X
GROUP BY
X.year_quarter
) B
WHERE
A.subquery_year_month=year_month
AND B.subquery_year_quarter=year_quarter


ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN is_last_day_in_month INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN is_last_day_in_quarter INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN days_in_month INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN weekdays_in_month INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN days_in_quarter INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN quarter_days_remaining INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN weekdays_in_quarter INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN year_days_remaining INT NOT NULL


----------------------------------------------------------------------------------------------------------------------
-- Table Update IV: Populate Additional Date Xref Table Fields (Pass IV)
----------------------------------------------------------------------------------------------------------------------

UPDATE
dbo.UTIL_DateCalendar
SET
month_weekdays_remaining=
weekdays_in_month - A.month_weekdays_remaining_subtraction
,quarter_weekdays_remaining=
weekdays_in_quarter - A.quarter_weekdays_remaining_subtraction
FROM
(
SELECT
X.calendar_date AS subquery_calendar_date
,ROW_NUMBER() OVER (PARTITION BY X.year_month ORDER BY X.calendar_date) AS month_weekdays_remaining_subtraction
,ROW_NUMBER() OVER (PARTITION BY X.year_quarter ORDER BY X.calendar_date) AS quarter_weekdays_remaining_subtraction
FROM
dbo.UTIL_DateCalendar X
WHERE
X.is_weekday=1
) A
WHERE
A.subquery_calendar_date=calendar_date


----------------------------------------------------------------------------------------------------------------------
-- Table Update V: Populate Additional Date Xref Table Fields (Pass V)
----------------------------------------------------------------------------------------------------------------------

UPDATE
dbo.UTIL_DateCalendar
SET
month_weekdays_remaining=A.month_weekdays_remaining
,quarter_weekdays_remaining=A.quarter_weekdays_remaining
FROM
(
SELECT
X.calendar_date AS subquery_calendar_date
,COALESCE(Y.month_weekdays_remaining,
Z.month_weekdays_remaining, X.weekdays_in_month) AS month_weekdays_remaining
,COALESCE(Y.quarter_weekdays_remaining,
Z.quarter_weekdays_remaining,
X.weekdays_in_quarter) AS quarter_weekdays_remaining
FROM
dbo.UTIL_DateCalendar X
LEFT JOIN dbo.UTIL_DateCalendar Y
ON DATEADD(DAY, 1, Y.calendar_date)=X.calendar_date
AND Y.year_month=X.year_month
LEFT JOIN dbo.UTIL_DateCalendar Z
ON DATEADD(DAY, 2, Z.calendar_date)=X.calendar_date
AND Z.year_month=X.year_month
WHERE
X.month_weekdays_remaining IS NULL
) A
WHERE
A.subquery_calendar_date=calendar_date


ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN month_weekdays_remaining INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN quarter_weekdays_remaining INT NOT NULL


----------------------------------------------------------------------------------------------------------------------
-- Table Update VI: Populate Additional Date Xref Table Fields (Pass VI)
----------------------------------------------------------------------------------------------------------------------

UPDATE
dbo.UTIL_DateCalendar
SET
is_week_in_same_month=A.is_week_in_same_month
,month_days_remaining=days_in_month-calendar_day
,month_weekdays_completed=
weekdays_in_month - month_weekdays_remaining
,quarter_days_completed=days_in_quarter-quarter_days_remaining
,quarter_weekdays_completed=
weekdays_in_quarter - quarter_weekdays_remaining
,month_day_name_instance=A.month_day_name_instance
,quarter_day_name_instance=A.quarter_day_name_instance
,year_day_name_instance=A.year_day_name_instance

FROM
(
SELECT
X.calendar_date AS subquery_calendar_date
,ISNULL((
CASE
WHEN DATEDIFF(MONTH, X.first_day_in_week,X.last_day_in_week)=0 THEN 1
END), 0)
AS is_week_in_same_month
,ROW_NUMBER() OVER (PARTITION BY X.year_month, X.day_name ORDER BY X.calendar_date) AS month_day_name_instance
,ROW_NUMBER() OVER (PARTITION BY X.year_quarter, X.day_name ORDER BY X.calendar_date) AS quarter_day_name_instance
,ROW_NUMBER() OVER (
PARTITION BY X.calendar_year,X.day_name
ORDER BY X.calendar_date)
AS year_day_name_instance
FROM
dbo.UTIL_DateCalendar X
) A
WHERE
A.subquery_calendar_date=calendar_date


ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN is_week_in_same_month INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN month_days_remaining INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN month_weekdays_completed INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN quarter_days_completed INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN quarter_weekdays_completed INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN month_day_name_instance INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN quarter_day_name_instance INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN year_day_name_instance INT NOT NULL


----------------------------------------------------------------------------------------------------------------------
-- Main Query: Final Display / Output
----------------------------------------------------------------------------------------------------------------------

SELECT
URD.*
,[holiday] = CASE WHEN MONTH(URD.calendar_date)=1
AND DAY(URD.calendar_date)=1
THEN 'New Years Day'
WHEN MONTH(URD.calendar_date)=5
AND DATEPART(weekday,
DATEADD(day, @@DATEFIRST,
URD.calendar_date))=2
AND DAY(URD.calendar_date) BETWEEN 25 AND 31
THEN 'Memorial Day'
WHEN MONTH(URD.calendar_date)=7
AND DAY(URD.calendar_date)=4
THEN 'Independence Day'
WHEN MONTH(URD.calendar_date)=9
AND DATEPART(weekday,
DATEADD(day, @@DATEFIRST,
URD.calendar_date))=2
AND DAY(URD.calendar_date) BETWEEN 1 AND 7
THEN 'Labor Day'
WHEN MONTH(URD.calendar_date)=11
AND DATEPART(weekday,
DATEADD(day, @@DATEFIRST,
URD.calendar_date))=5
AND DAY(URD.calendar_date) BETWEEN 22 AND 28
THEN 'Thanksgiving'
WHEN MONTH(URD.calendar_date)=12
AND DAY(URD.calendar_date)=25
THEN 'Christmas Eve'
WHEN MONTH(URD.calendar_date)=12
AND DAY(URD.calendar_date)=26
THEN 'Christmas Day'
ELSE '' -- or NULL depending on your preference
END
,[ordinal_num] = CAST(DAY(URD.calendar_day) AS NVARCHAR(2))
+CASE WHEN CAST(DAY(URD.calendar_day) AS INT) IN (1, 21, 31)
THEN N'st'
WHEN CAST(DAY(URD.calendar_day) AS INT) IN (2, 22)
THEN N'nd'
WHEN CAST(DAY(URD.calendar_day) AS INT) IN (3, 23)
THEN N'rd'
ELSE N'th'
END
,[ordinal_txt] = CASE WHEN CAST(DAY(URD.calendar_day) AS INT)=1
THEN N'First'
WHEN CAST(DAY(URD.calendar_day) AS INT)=2
THEN N'Second'
WHEN CAST(DAY(URD.calendar_day) AS INT)=3
THEN N'Third'
WHEN CAST(DAY(URD.calendar_day) AS INT)=4
THEN N'Fourth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=5
THEN N'Fifth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=6
THEN N'Sixth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=7
THEN N'Seventh'
WHEN CAST(DAY(URD.calendar_day) AS INT)=8
THEN N'Eighth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=9
THEN N'Ninth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=10
THEN N'Tenth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=11
THEN N'Elevent'
WHEN CAST(DAY(URD.calendar_day) AS INT)=12
THEN N'Twelfth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=13
THEN N'Thirteenth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=14
THEN N'Fourteenth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=15
THEN N'Fifteenth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=16
THEN N'Sixteenth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=17
THEN N'Seventeenth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=18
THEN N'Eighteenth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=19
THEN N'Nineteenth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=20
THEN N'Twentieth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=21
THEN N'Twenty-first'
WHEN CAST(DAY(URD.calendar_day) AS INT)=22
THEN N'Twenty-second'
WHEN CAST(DAY(URD.calendar_day) AS INT)=23
THEN N'Twenty-third'
WHEN CAST(DAY(URD.calendar_day) AS INT)=24
THEN N'Twenty-fourth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=25
THEN N'Twenty-fifth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=26
THEN N'Twenty-sixth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=27
THEN N'Twenty-seventh'
WHEN CAST(DAY(URD.calendar_day) AS INT)=28
THEN N'Twenty-eighth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=29
THEN N'Twenty-ninth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=30
THEN N'Thirtierth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=31
THEN N'Thirty-first'
END
FROM
dbo.UTIL_DateCalendar URD
ORDER BY
URD.calendar_date

END


Post #1493973
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse