CALCDATE
Updated 2023-10-06 23:37:58.217000
Syntax
SELECT [westclintech].[wct].[CALCDATE](
<@Year, int,>
,<@Month, int,>
,<@Day, int,>)
Description
Use the scalar function CALCDATE to return a datetime value for a specified Year, Month, and Day.
Arguments
@Year
The integer value representing the year. @Year is an expression that returns an int or that can be implicitly converted to int.
@Month
The integer value representing the month. @Month is an expression that returns an int or that can be implicitly converted to int.
@Day
The integer value representing the day of the month. @Day is an expression that returns an int or that can be implicitly converted to int.
Return Type
datetime
Remarks
CALCDATE is the equivalent of the DATE function in EXCEL, except that it returns a datetime value instead of integer.
To convert a year, month, and day combination to an integer, consider using the DATEINT function.
To convert a year, month, and day combination to an integer, consider using the DATEFLOAT function.
Examples
To represent 07-Feb-2011, enter the following SQL.
SELECT wct.CALCDATE( 2011, --@Year
2, --@Month
7 --@Day
) as DATE;
This produces the following result.
{"columns":[{"field":"DATE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"DATE":"2011-02-07 00:00:00.000"}]}
CALCDATE is language independent. The datetime value returned is consistent across different language settings. Look at the following example.
SET LANGUAGE Italian;
SELECT wct.CALCDATE(2011, 2, 7) as [CALCDATE(2011,2,7)],
cast('02/07/2011' as datetime) as [cast('02/07/2011' as datetime)];
This returns the following result.
{"columns":[{"field":"CALCDATE(2011,2,7)","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"cast('02/07/2011' as datetime)","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"CALCDATE(2011,2,7)":"2011-02-07 00:00:00.000","cast('02/07/2011' as datetime)":"2011-07-02 00:00:00.000"}]}
SET LANGUAGE us_english;
SELECT wct.CALCDATE(2011, 2, 7) as [CALCDATE(2011,2,7)],
cast('02/07/2011' as datetime) as [cast('02/07/2011' as datetime)];
This returns the following result.
{"columns":[{"field":"CALCDATE(2011,2,7)","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"CAST('02/07/2011' AS datetime)","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"CALCDATE(2011,2,7)":"2011-02-07 00:00:00.000","CAST('02/07/2011' AS datetime)":"2011-02-07 00:00:00.000"}]}
As you can see, the same date string was passed in both statements, but the datetime value returned was different based upon the language setting for the database. Using CALCDATE avoids that problem.
CALCDATE allows you to perform operation on the date components. You can add or subtract integer values to @Year, @Month, @Date, and still be confident of having a valid value returned.
Let’s say you wanted to generate a schedule that showed the first Monday of every month. Here’s one way to do this in SQL.
SELECT DATEADD(
WEEK,
DATEDIFF(
WEEK,
0,
(DATEADD(
DAY,
(6 - DATEPART(DAY, DATEFROMPARTS(2011,
m.mth, 1))),
DATEFROMPARTS(2011, m.mth, 1)
)
)
),
0
)
FROM
(
VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10),
(11),
(12)
) m (mth);
This produces the following result.
{"columns":[{"field":"First Monday","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"First Monday":"2011-01-03 00:00:00.000"},{"First Monday":"2011-02-07 00:00:00.000"},{"First Monday":"2011-03-07 00:00:00.000"},{"First Monday":"2011-04-04 00:00:00.000"},{"First Monday":"2011-05-02 00:00:00.000"},{"First Monday":"2011-06-06 00:00:00.000"},{"First Monday":"2011-07-04 00:00:00.000"},{"First Monday":"2011-08-01 00:00:00.000"},{"First Monday":"2011-09-05 00:00:00.000"},{"First Monday":"2011-10-03 00:00:00.000"},{"First Monday":"2011-11-07 00:00:00.000"},{"First Monday":"2011-12-05 00:00:00.000"}]}
CALCDATE allows you to perform operations on the components of the date rather than on the date value, simplifying the SQL.
SELECT wct.CALCDATE(2011, mth, (9 - DATEPART(DW, wct.CALCDATE(2011, mth, 1))) % 8)
as [First Monday]
FROM
(
VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10),
(11),
(12)
) m (mth);
This produces the following result.
{"columns":[{"field":"First Monday","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"First Monday":"2011-01-03 00:00:00.000"},{"First Monday":"2011-02-07 00:00:00.000"},{"First Monday":"2011-03-07 00:00:00.000"},{"First Monday":"2011-04-04 00:00:00.000"},{"First Monday":"2011-05-02 00:00:00.000"},{"First Monday":"2011-06-06 00:00:00.000"},{"First Monday":"2011-07-04 00:00:00.000"},{"First Monday":"2011-08-01 00:00:00.000"},{"First Monday":"2011-09-05 00:00:00.000"},{"First Monday":"2011-10-03 00:00:00.000"},{"First Monday":"2011-11-07 00:00:00.000"},{"First Monday":"2011-12-05 00:00:00.000"}]}
The SQL could be further simplified by using the XLeratorDB Modulo function as the SQL Server % , is really a remainder function. The SQL would look more intuitive if we had used the XLeratorDB modulo function (which is the equivalent of the EXCEL MOD function).
SELECT wct.CALCDATE(2011, mth, 1 + wct.Modulo((2 - DATEPART(DW, wct.CALCDATE(2011,
mth, 1))), 7)) as [First Monday]
FROM
(
VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10),
(11),
(12)
) m (mth);
This produces the following result.
{"columns":[{"field":"First Monday","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"First Monday":"2011-01-03 00:00:00.000"},{"First Monday":"2011-02-07 00:00:00.000"},{"First Monday":"2011-03-07 00:00:00.000"},{"First Monday":"2011-04-04 00:00:00.000"},{"First Monday":"2011-05-02 00:00:00.000"},{"First Monday":"2011-06-06 00:00:00.000"},{"First Monday":"2011-07-04 00:00:00.000"},{"First Monday":"2011-08-01 00:00:00.000"},{"First Monday":"2011-09-05 00:00:00.000"},{"First Monday":"2011-10-03 00:00:00.000"},{"First Monday":"2011-11-07 00:00:00.000"},{"First Monday":"2011-12-05 00:00:00.000"}]}
In this example, we will calculate the date that US Thanksgiving falls on for the years 2011 through 2020. US Thanksgiving falls on the fourth Thursday in November. Therefore, if the first of November is a Thursday, the earliest that Thanksgiving can occur is on the 22nd of November. Thursday is day number 5.
SELECT wct.CALCDATE(yr, 11, 22 + wct.Modulo((5 - DATEPART(DW, wct.CALCDATE(yr, 11,
22))), 7)) as [US Thanksgiving]
FROM
(
VALUES
(2011),
(2012),
(2013),
(2014),
(2015),
(2016),
(2017),
(2018),
(2019),
(2020)
) m (yr);
This produces the following result.
{"columns":[{"field":"US Thanksgiving","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"US Thanksgiving":"2011-11-24 00:00:00.000"},{"US Thanksgiving":"2012-11-22 00:00:00.000"},{"US Thanksgiving":"2013-11-28 00:00:00.000"},{"US Thanksgiving":"2014-11-27 00:00:00.000"},{"US Thanksgiving":"2015-11-26 00:00:00.000"},{"US Thanksgiving":"2016-11-24 00:00:00.000"},{"US Thanksgiving":"2017-11-23 00:00:00.000"},{"US Thanksgiving":"2018-11-22 00:00:00.000"},{"US Thanksgiving":"2019-11-28 00:00:00.000"},{"US Thanksgiving":"2020-11-26 00:00:00.000"}]}
In this example, we will calculate the day that the Christmas holiday is observed. Christmas is observed on the 25th of December. However, if the Christmas holiday falls on a weekend, it is observed on the following Monday.
SELECT CASE DATEPART(DW, wct.CALCDATE(yr, 12, 25))
WHEN 1 THEN
wct.CALCDATE(yr, 12, 26)
WHEN 7 THEN
wct.CALCDATE(yr, 12, 27)
ELSE
wct.CALCDATE(yr, 12, 25)
END as Christmas
FROM
(
VALUES
(2011),
(2012),
(2013),
(2014),
(2015),
(2016),
(2017),
(2018),
(2019),
(2020)
) m (yr);
]This produces the following result.
{"columns":[{"field":"Christmas","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Christmas":"2011-12-26 00:00:00.000"},{"Christmas":"2012-12-25 00:00:00.000"},{"Christmas":"2013-12-25 00:00:00.000"},{"Christmas":"2014-12-25 00:00:00.000"},{"Christmas":"2015-12-25 00:00:00.000"},{"Christmas":"2016-12-26 00:00:00.000"},{"Christmas":"2017-12-25 00:00:00.000"},{"Christmas":"2018-12-25 00:00:00.000"},{"Christmas":"2019-12-25 00:00:00.000"},{"Christmas":"2020-12-25 00:00:00.000"}]}
CALCDATE can simplify date calculations on the database. Let’s say that you are a company that has a 52-53 week fiscal year, and the year end is defined as the final Saturday in the year end month. You need to calculate the year end date and the number of days in the year.
DECLARE @YEM as int = 8;
SELECT yr,
CAST([Last Saturday] as datetime) as [Last Saturday],
DATEDIFF(d, [Last Saturday PY], [Last Saturday]) as [Days in Year]
FROM
(
SELECT yr,
wct.CALCDATE(
yr,
@YEM,
DATEPART(d, wct.CALCDATE(yr, @YEM + 1, 1) - 7)
+ wct.Modulo((7 - DATEPART(DW, wct.CALCDATE(yr, @YEM +
1, 1) - 7)), 7)
) as [Last Saturday],
wct.CALCDATE(
yr - 1,
@YEM,
DATEPART(d, wct.CALCDATE(yr - 1, @YEM + 1, 1) - 7)
+ wct.Modulo((7 - DATEPART(DW, wct.CALCDATE(yr - 1,
@YEM + 1, 1) - 7)), 7)
) as [Last Saturday PY]
FROM
(
VALUES
(2005),
(2006),
(2007),
(2008),
(2009),
(2010),
(2011),
(2012),
(2013),
(2014),
(2015),
(2016),
(2017),
(2018),
(2019)
) n (yr)
) m;
This produces the following result.
{"columns":[{"field":"yr","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Last Saturday","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Days in Year","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"yr":"2005","Last Saturday":"2005-08-27 00:00:00.000","Days in Year":"364"},{"yr":"2006","Last Saturday":"2006-08-26 00:00:00.000","Days in Year":"364"},{"yr":"2007","Last Saturday":"2007-08-25 00:00:00.000","Days in Year":"364"},{"yr":"2008","Last Saturday":"2008-08-30 00:00:00.000","Days in Year":"371"},{"yr":"2009","Last Saturday":"2009-08-29 00:00:00.000","Days in Year":"364"},{"yr":"2010","Last Saturday":"2010-08-28 00:00:00.000","Days in Year":"364"},{"yr":"2011","Last Saturday":"2011-08-27 00:00:00.000","Days in Year":"364"},{"yr":"2012","Last Saturday":"2012-08-25 00:00:00.000","Days in Year":"364"},{"yr":"2013","Last Saturday":"2013-08-31 00:00:00.000","Days in Year":"371"},{"yr":"2014","Last Saturday":"2014-08-30 00:00:00.000","Days in Year":"364"},{"yr":"2015","Last Saturday":"2015-08-29 00:00:00.000","Days in Year":"364"},{"yr":"2016","Last Saturday":"2016-08-27 00:00:00.000","Days in Year":"364"},{"yr":"2017","Last Saturday":"2017-08-26 00:00:00.000","Days in Year":"364"},{"yr":"2018","Last Saturday":"2018-08-25 00:00:00.000","Days in Year":"364"},{"yr":"2019","Last Saturday":"2019-08-31 00:00:00.000","Days in Year":"371"}]}
In this example the year end date is the Saturday that falls closest to the last day of the fiscal year end month.
DECLARE @YEM as int = 8;
SELECT yr,
CAST(YE as datetime) as [YE Date],
DATEDIFF(d, PYE, YE) as [Days in Year]
FROM
(
SELECT yr,
wct.CALCDATE(
yr,
@YEM,
DATEPART(d, wct.CALCDATE(yr, @YEM + 1, 1) - 4)
+ wct.Modulo((7 - DATEPART(DW, wct.CALCDATE(yr, @YEM +
1, 1) - 4)), 7)
) as YE,
wct.CALCDATE(
yr - 1,
@YEM,
DATEPART(d, wct.CALCDATE(yr - 1, @YEM + 1, 1) - 4)
+ wct.Modulo((7 - DATEPART(DW, wct.CALCDATE(yr - 1,
@YEM + 1, 1) - 4)), 7)
) as PYE
FROM
(
VALUES
(2005),
(2006),
(2007),
(2008),
(2009),
(2010),
(2011),
(2012),
(2013),
(2014),
(2015),
(2016),
(2017),
(2018),
(2019)
) n (yr)
) m;
This produces the following result.
{"columns":[{"field":"yr","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"YE Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Days in Year","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"yr":"2005","YE Date":"2005-09-03 00:00:00.000","Days in Year":"371"},{"yr":"2006","YE Date":"2006-09-02 00:00:00.000","Days in Year":"364"},{"yr":"2007","YE Date":"2007-09-01 00:00:00.000","Days in Year":"364"},{"yr":"2008","YE Date":"2008-08-30 00:00:00.000","Days in Year":"364"},{"yr":"2009","YE Date":"2009-08-29 00:00:00.000","Days in Year":"364"},{"yr":"2010","YE Date":"2010-08-28 00:00:00.000","Days in Year":"364"},{"yr":"2011","YE Date":"2011-09-03 00:00:00.000","Days in Year":"371"},{"yr":"2012","YE Date":"2012-09-01 00:00:00.000","Days in Year":"364"},{"yr":"2013","YE Date":"2013-08-31 00:00:00.000","Days in Year":"364"},{"yr":"2014","YE Date":"2014-08-30 00:00:00.000","Days in Year":"364"},{"yr":"2015","YE Date":"2015-08-29 00:00:00.000","Days in Year":"364"},{"yr":"2016","YE Date":"2016-09-03 00:00:00.000","Days in Year":"371"},{"yr":"2017","YE Date":"2017-09-02 00:00:00.000","Days in Year":"364"},{"yr":"2018","YE Date":"2018-09-01 00:00:00.000","Days in Year":"364"},{"yr":"2019","YE Date":"2019-08-31 00:00:00.000","Days in Year":"364"}]}