Skip to content

Instantly share code, notes, and snippets.

@BDollar

BDollar/viewCommonDates

Created April 30, 2013 16:18
Show Gist options
  • Save BDollar/5489796 to your computer and use it in GitHub Desktop.
Save BDollar/5489796 to your computer and use it in GitHub Desktop.
Download ZIP
View that generates commonly used dates for SQL environments.
Raw
viewCommonDates
USE [master]
GO
/****** Object: View [dbo].[viewCommonDates] Script Date: 04/30/2013 12:14:20 ******/
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[viewCommonDates]'))
DROP VIEW [dbo].[viewCommonDates]
GO
USE [master]
GO
/****** Object: View [dbo].[viewCommonDates] Script Date: 04/30/2013 12:14:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[viewCommonDates] AS
/* The view is made of a group of unioned queries. The next line, and others similar to it make up the
[Description] column. The description column is the requested time of the dynamic date. */
SELECT [Grid].[Description] AS [Description]
, [Grid].[Julian] AS [Julian]
, [Grid].[Gregorian] AS [Gregorian]
FROM (
SELECT 'Today' AS [Description],
/* The second column represents the date in Julian format. */
/* The Julian date for the AS400 starts at the year 1900 with 000001, so we have to ake the Gregorian year
and subtract 1900, and put three zeros behind it to get the julian year.
Year - 1900 x 1000 = Julian Year
2008 - 1900 = 108 x 1000 = 108000
*/
(
DATEPART
( yyyy,
GETDATE()
) - 1900
) * 1000
/*
Day of the Year
number representing the year + day of the year
1/30/08 = 30
108000 + 30 = 108030
*/
+
( DATEPART
( DY,
GETDATE()
)
) AS [Julian],
/* The third column represents the date in Gregorian format. */
/*
Sets the current month number as two digits and add a slash mark '01/'
*/
CAST
(
CAST
(
DATEPART
(
Month,
GETDATE()
) AS VARCHAR(2)
)+'/'+
/*
Adds the current day of the month as two digits and add a slash mark '01/30/'
*/
CAST
(
DATEPART
(
DAY,
GETDATE()
)AS VARCHAR (2)
)+'/'+
/*
Adds the current year as four digits '01/30/08'
*/
CAST
(
DATEPART
(
YEAR,
GETDATE()
)AS VARCHAR (4)
)AS DATETIME
)AS [Gregorian]
/*
UNION glues queries together as one. ALL prevents the union from combining the rows.
*/
UNION ALL
SELECT 'BeginningOfPreviousMonth' AS [Description],
/*
Similar to 'EndOfLastMonth', this query also subtracts 3 months from the date.
*/
(
DATEPART
( yyyy,
DATEADD
(
DAY,
DATEPART
(
DAY,
DATEADD
(
MONTH,
-1,
GETDATE()
)
)*-1,
DATEADD
(
MONTH,
-1,
GETDATE()
)+1
)
) - 1900
) * 1000
+
( DATEPART
(
DY,
DATEADD
(
DAY,
DATEPART
(
DAY,
DATEADD
(
MONTH,
-1,
GETDATE()
)
)*-1,
DATEADD
(
MONTH,
-1,
GETDATE()
)+1
)
)
) AS [Julian],
CAST
(
CAST
(
DATEPART
(
Month,
DATEADD
(
MONTH,
-1,
GETDATE()
)
) AS VARCHAR(2)
) +'/1/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
MONTH,
-1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
) AS [Gregorian]
UNION ALL
SELECT 'BeginningOfNextMonth' AS [Description],
/*
Similar to 'EndOfLastMonth', this query also subtracts 3 months from the date.
*/
(
DATEPART
( yyyy,
DATEADD
(
DAY,
DATEPART
(
DAY,
DATEADD
(
MONTH,
1,
GETDATE()
)
)*-1,
DATEADD
(
MONTH,
1,
GETDATE()
)+1
)
) - 1900
) * 1000
+
( DATEPART
(
DY,
DATEADD
(
DAY,
DATEPART
(
DAY,
DATEADD
(
MONTH,
1,
GETDATE()
)
)*-1,
DATEADD
(
MONTH,
1,
GETDATE()
)+1
)
)
) AS [Julian],
CAST
(
CAST
(
DATEPART
(
Month,
DATEADD
(
MONTH,
1,
GETDATE()
)
) AS VARCHAR(2)
) +'/1/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
MONTH,
1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
) AS [Gregorian]
UNION ALL
/*
Adds a different description on a new row in the description column.
*/
SELECT 'EndOfPreviousMonth' AS [Description],
/*
Takes the day of the month and subtracts it from the days in the current date to get the last day of the
previous month. Then it takes the year of that date and subtracts 1900, multiplies the result times 1000,
and adds it to the year number.
*/
(
DATEPART
( yyyy,
DATEADD
( DAY,
DATEPART
( DAY,
GETDATE()
)*-1,
GETDATE()
)
) - 1900
) * 1000
+
/*
Takes the day of the month and subtracts it from the days in the current date to get the last day of the
previous month. Then it translates it into the day of the year and adds it to the year number.
*/
( DATEPART
( DY,
DATEADD
( DAY,
DATEPART
( DAY,
GETDATE()
)*-1,
GETDATE()
)
)
) AS [Julian],
/*
Takes the current date, and replaces the day of the month with 1. Then subtracts 1 day.
*/
DATEADD
(
DAY,-1,
CAST
(
CAST
(
DATEPART
(
Month,
GETDATE()
) AS VARCHAR(2)
) +'/1/'+
CAST
(
DATEPART
(
YEAR,
GETDATE()
)AS VARCHAR (4)
)AS DATETIME
)
) AS [Gregorian]
UNION ALL
SELECT 'EndOfNextMonth' AS [Description],
/*
Takes the day of the month and subtracts it from the days in the current date to get the last day of the
previous month. Then it takes the year of that date and subtracts 1900, multiplies the result times 1000,
and adds it to the year number.
*/
(
DATEPART
( yyyy,
DATEADD
( DAY,
DATEPART
( DAY,
DATEADD
(
MONTH,
2,
GETDATE()
)
)*-1,
DATEADD
(
MONTH,
2,
GETDATE()
)
)
) - 1900
) * 1000
+
/*
Takes the day of the month, adds two months, and subtracts it from itself to get the last day of the
next month. Then it translates it into the day of the year and adds it to the year number.
*/
( DATEPART
( DY,
DATEADD
( DAY,
DATEPART
( DAY,
DATEADD
(
MONTH,
2,
GETDATE()
)
)*-1,
DATEADD
(
MONTH,
2,
GETDATE()
)
)
)
) AS [Julian],
/*
Takes the current date, and replaces the day of the month with 1. Then subtracts 1 day.
*/
DATEADD
(
DAY,-1,
CAST
(
CAST
(
DATEPART
(
Month,
DATEADD
(
MONTH,
2,
GETDATE()
)
) AS VARCHAR(2)
) +'/1/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
MONTH,
2,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
)
) AS [Gregorian]
UNION ALL
/*
Adds a different description on a new row in the description column.
*/
SELECT 'BeginningOfCurrentMonth' AS [Description],
/*
Takes the day of the month -1 and subtracts it from the days in the current date to get the 1st day of the current month. Then it takes the year of that date and subtracts 1900, multiplies the result times 1000,
and adds it to the year number.
*/
(
DATEPART
( yyyy,
DATEADD
( DAY,
DATEPART
( DAY,
GETDATE()-1
)*-1,
GETDATE()
)
) - 1900
) * 1000
+
/*
Takes the day of the month and subtracts it from the days in the current date to get the last day of the
previous month. Then it translates it into the day of the year and adds it to the year number.
*/
( DATEPART
( DY,
DATEADD
( DAY,
DATEPART
( DAY,
GETDATE()-1
)*-1,
GETDATE()
)
)
) AS [Julian],
/*
Takes the current date, and replaces the day of the month with 1.
*/
DATEADD
(
DAY,0,
CAST
(
CAST
(
DATEPART
(
Month,
GETDATE()
) AS VARCHAR(2)
) +'/1/'+
CAST
(
DATEPART
(
YEAR,
GETDATE()
)AS VARCHAR (4)
)AS DATETIME
)
) AS [Gregorian]
UNION ALL
SELECT 'EndOfCurrentMonth' AS [Description],
(
DATEPART
( yyyy,
DATEADD
(
DAY,
DATEPART
(
DAY,
DATEADD
(
MONTH,
+1,
GETDATE()
)
)*-1,
DATEADD
(
MONTH,
+1,
GETDATE()
)
)
) - 1900
) * 1000
+
( DATEPART
(
DY,
DATEADD
(
DAY,
DATEPART
(
DAY,
DATEADD
(
MONTH,
+1,
GETDATE()
)
)*-1,
DATEADD
(
MONTH,
+1,
GETDATE()
)
)
)
) AS [Julian],
DATEADD
(
DAY,-1,
CAST
(
CAST
(
DATEPART
(
Month,
DATEADD
(
MONTH,
+1,
GETDATE()
)
) AS VARCHAR(2)
) +'/1/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
MONTH,
+1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
)
) AS [Gregorian]
UNION ALL
SELECT 'BeginningOf03MonthsAgo' AS [Description],
/*
Similar to 'EndOfLastMonth', this query also subtracts 3 months from the date.
*/
(
DATEPART
( yyyy,
DATEADD
(
DAY,
DATEPART
(
DAY,
DATEADD
(
MONTH,
-3,
GETDATE()
)
)*-1,
DATEADD
(
MONTH,
-3,
GETDATE()
)+1
)
) - 1900
) * 1000
+
( DATEPART
(
DY,
DATEADD
(
DAY,
DATEPART
(
DAY,
DATEADD
(
MONTH,
-3,
GETDATE()
)
)*-1,
DATEADD
(
MONTH,
-3,
GETDATE()
)+1
)
)
) AS [Julian],
CAST
(
CAST
(
DATEPART
(
Month,
DATEADD
(
MONTH,
-3,
GETDATE()
)
) AS VARCHAR(2)
) +'/1/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
MONTH,
-3,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
) AS [Gregorian]
UNION ALL
SELECT 'BeginningOf06MonthsAgo' AS [Description],
/*
Similar to 'EndOfLastMonth', this query also subtracts 6 months from the date.
*/
(
DATEPART
( yyyy,
DATEADD
(
DAY,
DATEPART
(
DAY,
DATEADD
(
MONTH,
-6,
GETDATE()
)
)*-1,
DATEADD
(
MONTH,
-6,
GETDATE()
)+1
)
) - 1900
) * 1000
+
( DATEPART
(
DY,
DATEADD
(
DAY,
DATEPART
(
DAY,
DATEADD
(
MONTH,
-6,
GETDATE()
)
)*-1,
DATEADD
(
MONTH,
-6,
GETDATE()
)+1
)
)
) AS [Julian],
CAST
(
CAST
(
DATEPART
(
Month,
DATEADD
(
MONTH,
-6,
GETDATE()
)
) AS VARCHAR(2)
) +'/1/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
MONTH,
-6,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
) AS [Gregorian]
UNION ALL
SELECT 'BeginningOf12MonthsAgo' AS [Description],
/*
Similar to 'EndOfLastMonth', this query also subtracts 12 months from the date.
*/
(
DATEPART
( yyyy,
DATEADD
( MONTH,
-12,
DATEADD
( DAY,
DATEPART
(
DAY,
GETDATE()
)*-1,
GETDATE()
)
)
) - 1900
) * 1000
+
( DATEPART
( DY,
DATEADD
( MONTH,
-12,
DATEADD
( DAY,
DATEPART
(
DAY,
GETDATE()
)*-1,
GETDATE()
)
)
)+1
)AS [Julian],
CAST
(
CAST
(
DATEPART
(
Month,
DATEADD
(
MONTH,
-12,
GETDATE()
)
) AS VARCHAR(2)
) +'/1/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
MONTH,
-12,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
) AS [Gregorian]
UNION ALL
SELECT 'BeginningOfPreviousYear' AS [Description],
/*
Takes the year from the current date and subtracts 1 year, then subtracts 1900 from the result
and multiplies it times 1000. Finally, one is added to the number ot get the first day of the year.
*/
(
DATEPART
( yyyy,
DATEADD
(
YYYY,
-1,
GETDATE()
)
) - 1900
) * 1000
+ 1 AS [Julian],
/*
Takes the year from the current date and subtracts one year. The result is converted to a
4 character string and attached to the end of '01/01/'. The string is then converted back
to a date.
*/
CAST
(
'01/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
-1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
) AS [Gregorian]
UNION ALL
/*
Takes the year from the current date and subtracts 1900, then multiplies the result times 1000
and adds one (representing the first day of the year).
*/
SELECT 'BeginningOfCurrentYear' AS [Description],
(
DATEPART
( yyyy,
GETDATE()
) - 1900
) * 1000
+ 1 AS [Julian],
/*
Takes the year from the current date. The year is converted to a 4 character string and attached
to the end of '01/01/'. The string is then converted back to a date.
*/
CAST
(
'01/01/'+
CAST
(
DATEPART
(
YEAR,
GETDATE()
)AS VARCHAR (4)
)AS DATETIME
) AS [Gregorian]
UNION ALL
/*
Takes the year from the current date and adds one year, then subtracts 1900, then multiplies the result times 1000
and adds one (representing the first day of the year).
*/
SELECT 'BeginningOfNextYear' AS [Description],
(
DATEPART
( yyyy,
DATEADD
(
YYYY,
+1,
GETDATE()
)
) - 1900
) * 1000
+ 1 AS [Julian],
/*
Takes the year from the current date and adds one year. The result is converted to a
4 character string and attached to the end of '01/01/'. The string is then converted back
to a date.
*/
CAST
(
'01/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
+1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
) AS [Gregorian]
UNION ALL
SELECT 'BeginningOf1stQuarterLY' AS [Description],
(
DATEPART
(
yyyy,
DATEADD
(
YYYY,
-1,
GETDATE()
)
) - 1900
) * 1000
+
DATEPART
(
dy,
CAST
(
'01/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
-1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
)
)AS [Julian],
CAST
(
'01/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
-1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
) AS [Gregorian]
UNION ALL
SELECT 'BeginningOf2ndQuarterLY' AS [Description],
(
DATEPART
(
yyyy,
DATEADD
(
YYYY,
-1,
GETDATE()
)
) - 1900
) * 1000
+
DATEPART
(
dy,
CAST
(
'04/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
-1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
)
)AS [Julian],
CAST
(
'04/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
-1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
) AS [Gregorian]
UNION ALL
SELECT 'BeginningOf3rdQuarterLY' AS [Description],
(
DATEPART
(
yyyy,
DATEADD
(
YYYY,
-1,
GETDATE()
)
) - 1900
) * 1000
+
DATEPART
(
dy,
CAST
(
'07/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
-1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
)
)AS [Julian],
CAST
(
'07/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
-1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
) AS [Gregorian]
UNION ALL
SELECT 'BeginningOf4thQuarterLY' AS [Description],
(
DATEPART
(
yyyy,
DATEADD
(
YYYY,
-1,
GETDATE()
)
) - 1900
) * 1000
+
DATEPART
(
dy,
CAST
(
'10/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
-1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
)
)AS [Julian],
CAST
(
'10/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
-1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
) AS [Gregorian]
UNION ALL
SELECT 'BeginningOf1stQuarterTY' AS [Description],
(
DATEPART
(
yyyy,
GETDATE()
) - 1900
) * 1000
+
DATEPART
(
dy,
CAST
(
'01/01/'+
CAST
(
DATEPART
(
YEAR,
GETDATE()
)AS VARCHAR (4)
)AS DATETIME
)
)AS [Julian],
CAST
(
'01/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
+0,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
) AS [Gregorian]
UNION ALL
SELECT 'BeginningOf2ndQuarterTY' AS [Description],
(
DATEPART
(
yyyy,
GETDATE()
) - 1900
) * 1000
+
DATEPART
(
dy,
CAST
(
'04/01/'+
CAST
(
DATEPART
(
YEAR,
GETDATE()
)AS VARCHAR (4)
)AS DATETIME
)
)AS [Julian],
CAST
(
'04/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
+0,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
) AS [Gregorian]
UNION ALL
SELECT 'BeginningOf3rdQuarterTY' AS [Description],
(
DATEPART
(
yyyy,
GETDATE()
) - 1900
) * 1000
+
DATEPART
(
dy,
CAST
(
'07/01/'+
CAST
(
DATEPART
(
YEAR,
GETDATE()
)AS VARCHAR (4)
)AS DATETIME
)
)AS [Julian],
CAST
(
'07/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
+0,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
) AS [Gregorian]
UNION ALL
SELECT 'BeginningOf4thQuarterTY' AS [Description],
(
DATEPART
(
yyyy,
GETDATE()
) - 1900
) * 1000
+
DATEPART
(
dy,
CAST
(
'10/01/'+
CAST
(
DATEPART
(
YEAR,
GETDATE()
)AS VARCHAR (4)
)AS DATETIME
)
)AS [Julian],
CAST
(
'10/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
+0,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
) AS [Gregorian]
UNION ALL
SELECT 'EndOfPreviousYear' AS [Description],
(
DATEPART
( yyyy,
DATEADD
( DY,
-1,
DATEADD
( YYYY,
-1,
GETDATE()
)
)
) - 1900
) * 1000
+
(
DATEPART
( dy,
CAST
( '01/01/'
+
CAST
( DATEPART
(
YEAR,
DATEADD
(
YYYY,
+0,
GETDATE()
)
)
AS VARCHAR (4)
)
AS DATETIME
)-1
)
) AS [Julian],
CAST
(
'01/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
-0,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
)-1 AS [Gregorian]
UNION ALL
SELECT 'EndOfCurrentYear' AS [Description],
(
DATEPART
( yyyy,
DATEADD
( DY,
-1,
DATEADD
( YYYY,
+0,
GETDATE()
)
)
) - 1900
) * 1000
+
(
DATEPART
( dy,
CAST
( '01/01/'
+
CAST
( DATEPART
(
YEAR,
DATEADD
(
YYYY,
+1,
GETDATE()
)
)
AS VARCHAR (4)
)
AS DATETIME
)-1
)
) AS [Julian],
CAST
(
'01/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
+1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
)-1 AS [Gregorian]
UNION ALL
SELECT 'EndOfNextYear' AS [Description],
(
DATEPART
( yyyy,
DATEADD
( DY,
-1,
DATEADD
( YYYY,
+1,
GETDATE()
)
)
) - 1900
) * 1000
+
(
DATEPART
( dy,
CAST
( '01/01/'
+
CAST
( DATEPART
(
YEAR,
DATEADD
(
YYYY,
+2,
GETDATE()
)
)
AS VARCHAR (4)
)
AS DATETIME
)-1
)
) AS [Julian],
CAST
(
'01/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
+2,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
)-1 AS [Gregorian]
UNION ALL
SELECT 'EndOf1stQuarterLY' AS [Description],
(
DATEPART
(
yyyy,
DATEADD
(
YYYY,
-1,
GETDATE()
)
) - 1900
) * 1000
+
DATEPART
(
dy,
CAST
(
'04/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
-1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
)
)-1 AS [Julian],
CAST
(
'04/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
-1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
)-1 AS [Gregorian]
UNION ALL
SELECT 'EndOf2ndQuarterLY' AS [Description],
(
DATEPART
(
yyyy,
DATEADD
(
YYYY,
-1,
GETDATE()
)
) - 1900
) * 1000
+
DATEPART
(
dy,
CAST
(
'07/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
-1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
)
)-1 AS [Julian],
CAST
(
'07/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
-1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
)-1 AS [Gregorian]
UNION ALL
SELECT 'EndOf3rdQuarterLY' AS [Description],
(
DATEPART
(
yyyy,
DATEADD
(
YYYY,
-1,
GETDATE()
)
) - 1900
) * 1000
+
DATEPART
(
dy,
CAST
(
'10/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
-1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
)
)-1 AS [Julian],
CAST
(
'10/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
-1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
)-1 AS [Gregorian]
UNION ALL
SELECT 'EndOf4thQuarterLY' AS [Description],
(
DATEPART
( yyyy,
DATEADD
( YYYY,
-1,
GETDATE()
)
) - 1900
) * 1000
+
(
DATEPART
( dy,
CAST
(
'01/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
+0,
GETDATE()
)
)AS VARCHAR (4)
) AS DATETIME
)-1
)
) AS [Julian],
CAST
(
'01/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
+0,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
)-1 AS [Gregorian]
UNION ALL
SELECT 'EndOf1stQuarterTY' AS [Description],
(
DATEPART
(
yyyy,
GETDATE()
) - 1900
) * 1000
+
DATEPART
(
dy,
CAST
(
'04/01/'+
CAST
(
DATEPART
(
YEAR,
GETDATE()
)AS VARCHAR (4)
)AS DATETIME
)
)-1 AS [Julian],
CAST
(
'04/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
+0,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
)-1 AS [Gregorian]
UNION ALL
SELECT 'EndOf2ndQuarterTY' AS [Description],
(
DATEPART
(
yyyy,
GETDATE()
) - 1900
) * 1000
+
DATEPART
(
dy,
CAST
(
'07/01/'+
CAST
(
DATEPART
(
YEAR,
GETDATE()
)AS VARCHAR (4)
)AS DATETIME
)
)-1 AS [Julian],
CAST
(
'07/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
+0,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
)-1 AS [Gregorian]
UNION ALL
SELECT 'EndOf3rdQuarterTY' AS [Description],
(
DATEPART
(
yyyy,
GETDATE()
) - 1900
) * 1000
+
DATEPART
(
dy,
CAST
(
'10/01/'+
CAST
(
DATEPART
(
YEAR,
GETDATE()
)AS VARCHAR (4)
)AS DATETIME
)
)-1 AS [Julian],
CAST
(
'10/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
+0,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
)-1 AS [Gregorian]
UNION ALL
SELECT 'EndOf4thQuarterTY' AS [Description],
(
DATEPART
( yyyy,
DATEADD
( YYYY,
+0,
GETDATE()
)
) - 1900
) * 1000
+
(
DATEPART
( dy,
CAST
(
'01/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
+1,
GETDATE()
)
)AS VARCHAR (4)
) AS DATETIME
)-1
)
) AS [Julian],
CAST
(
'01/01/'+
CAST
(
DATEPART
(
YEAR,
DATEADD
(
YYYY,
+1,
GETDATE()
)
)AS VARCHAR (4)
)AS DATETIME
)-1 AS [Gregorian]
UNION ALL
SELECT 'PreviousWorkday' AS [Description]
, (
DATEPART
( yyyy,
DATEADD
(
DD,
CASE WHEN ((SELECT CAST(GETDATE()-1 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-1 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-2 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-2 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-3 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-3 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-4 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-4 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-5 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-5 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-6 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-6 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-7 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-7 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-8 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-8 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-9 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-9 AS DATE))) IN (1,7))) THEN
-10 ELSE -9 END ELSE -8 END ELSE -7 END ELSE -6 END ELSE -5 END ELSE -4 END ELSE -3 END ELSE -2 END ELSE -1 END,
(SELECT CAST(GETDATE() AS DATE))
)
) - 1900
) * 1000
+
( DATEPART
( DY,
DATEADD
(
DD,
CASE WHEN ((SELECT CAST(GETDATE()-1 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-1 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-2 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-2 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-3 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-3 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-4 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-4 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-5 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-5 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-6 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-6 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-7 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-7 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-8 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-8 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-9 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-9 AS DATE))) IN (1,7))) THEN
-10 ELSE -9 END ELSE -8 END ELSE -7 END ELSE -6 END ELSE -5 END ELSE -4 END ELSE -3 END ELSE -2 END ELSE -1 END,
(SELECT CAST(GETDATE() AS DATE))
)
)
) AS [Julian]
, DATEADD
(
DD,
CASE WHEN ((SELECT CAST(GETDATE()-1 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-1 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-2 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-2 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-3 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-3 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-4 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-4 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-5 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-5 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-6 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-6 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-7 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-7 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-8 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-8 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-9 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-9 AS DATE))) IN (1,7))) THEN
-10 ELSE -9 END ELSE -8 END ELSE -7 END ELSE -6 END ELSE -5 END ELSE -4 END ELSE -3 END ELSE -2 END ELSE -1 END,
(SELECT CAST(GETDATE() AS DATE))
) AS [Gregorian]
UNION
SELECT 'TwoWorkdaysAgo' AS [Description]
, (
DATEPART
( yyyy,
DATEADD
(
DD,
CASE WHEN ((SELECT CAST(GETDATE()-1 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-1 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-2 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-2 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-3 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-3 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-4 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-4 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-5 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-5 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-6 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-6 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-7 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-7 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-8 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-8 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-9 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-9 AS DATE))) IN (1,7))) THEN
-10 ELSE -9 END ELSE -8 END ELSE -7 END ELSE -6 END ELSE -5 END ELSE -4 END ELSE -3 END ELSE -2 END ELSE -1 END,
DATEADD
(
DD,
CASE WHEN ((SELECT CAST(GETDATE()-1 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-1 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-2 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-2 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-3 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-3 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-4 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-4 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-5 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-5 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-6 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-6 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-7 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-7 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-8 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-8 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-9 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-9 AS DATE))) IN (1,7))) THEN
-10 ELSE -9 END ELSE -8 END ELSE -7 END ELSE -6 END ELSE -5 END ELSE -4 END ELSE -3 END ELSE -2 END ELSE -1 END,
(SELECT CAST(GETDATE() AS DATE))
)
)
) - 1900
) * 1000
+
( DATEPART
( DY,
DATEADD
(
DD,
CASE WHEN ((SELECT CAST(GETDATE()-1 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-1 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-2 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-2 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-3 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-3 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-4 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-4 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-5 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-5 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-6 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-6 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-7 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-7 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-8 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-8 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-9 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-9 AS DATE))) IN (1,7))) THEN
-10 ELSE -9 END ELSE -8 END ELSE -7 END ELSE -6 END ELSE -5 END ELSE -4 END ELSE -3 END ELSE -2 END ELSE -1 END,
DATEADD
(
DD,
CASE WHEN ((SELECT CAST(GETDATE()-1 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-1 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-2 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-2 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-3 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-3 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-4 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-4 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-5 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-5 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-6 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-6 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-7 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-7 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-8 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-8 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-9 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-9 AS DATE))) IN (1,7))) THEN
-10 ELSE -9 END ELSE -8 END ELSE -7 END ELSE -6 END ELSE -5 END ELSE -4 END ELSE -3 END ELSE -2 END ELSE -1 END,
(SELECT CAST(GETDATE() AS DATE))
)
)
)
) AS [Julian]
, DATEADD
(
DD,
CASE WHEN ((SELECT CAST(GETDATE()-1 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-1 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-2 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-2 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-3 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-3 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-4 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-4 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-5 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-5 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-6 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-6 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-7 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-7 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-8 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-8 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-9 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-9 AS DATE))) IN (1,7))) THEN
-10 ELSE -9 END ELSE -8 END ELSE -7 END ELSE -6 END ELSE -5 END ELSE -4 END ELSE -3 END ELSE -2 END ELSE -1 END,
DATEADD
(
DD,
CASE WHEN ((SELECT CAST(GETDATE()-1 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-1 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-2 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-2 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-3 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-3 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-4 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-4 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-5 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-5 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-6 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-6 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-7 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-7 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-8 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-8 AS DATE))) IN (1,7))) THEN
CASE WHEN ((SELECT CAST(GETDATE()-9 AS DATE)) IN ( SELECT [tblITW_Holidays].[Date] FROM [master].[dbo].[tblITW_Holidays] ) OR (DATEPART(dw,(SELECT CAST(GETDATE()-9 AS DATE))) IN (1,7))) THEN
-10 ELSE -9 END ELSE -8 END ELSE -7 END ELSE -6 END ELSE -5 END ELSE -4 END ELSE -3 END ELSE -2 END ELSE -1 END,
(SELECT CAST(GETDATE() AS DATE))
)
) AS [Gregorian]
) AS [Grid]
GROUP BY [Grid].[Description]
, [Grid].[Julian]
, [Grid].[Gregorian]
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment