T-SQL is 'Transact-SQL', the version of SQL used with Microsoft SQL Server. This SQL code may be adaptable for other versions of SQL.
What is covered in this article
There are some things I need to find quite often
when working with dates, such as
This article shows ways to calculate all of these
using T-SQL. It then shows a version of a User Defined Function which
can be used for calculating these relative dates based on any input date.
|
Start date of the current month
The following will return the date of the start of the current month:
SELECT DATEADD(m, DATEDIFF(m,
0, GetDate()), 0)
It works in two steps:
First the DATEDIFF() counts how many whole months between now and the SQL zero date (the base datetime 1900-01-01 00:00:00.000)
First the DATEDIFF() counts how many whole months between now and the SQL zero date (the base datetime 1900-01-01 00:00:00.000)
Then the DATEADD()
adds that number of months back onto the zero date
Start date of the next month
The method above can be adapted to give the Start
Date of the Next Month by increasing the number of months to add by 1
SELECT DATEADD(m, DATEDIFF(m,
0, GetDate())+1, 0)
Other similar offsets can be calculated by altering
the +1 accordingly
End date of the current month
Versions from SQL Server 2012 have the built in function EOMONTH() for calculating the date of the end
of the month based on any given input date. For example
SELECT EOMONTH(Getdate())
returns 2017-12-31
This is fine if all you want is a Date. But I often
need the result as a DateTime when I am working with time based transaction
data. And EOMONTH() is not
available if you have a version older than SQL Server 2012
One way to derive the End Date of the Current Month
is to work from the Start Date of the Next Month (as calculated above) and then
deduct one second from it (Generally I only need this to be to the nearest
second - and remembering that deducting is Adding a negative number):
DATEADD(s,-1,<End
Date of Current Month>)
SELECT DATEADD(s,-1,(DATEADD(m, DATEDIFF(m, 0, GetDate())+1, 0)))
We could alternatively work from the Start Date of
the Current Month (as calculated above). This method adds one month onto the
start date - to give the start of the following month - and then deducts one
second from it
DATEADD(s,-1,(DATEADD(m,1,<Start Date of Current Month>)))
This method would be ok if we had already
calculated and stored the value of <Start Date of Current Month>. But
otherwise it is less efficient in a single expression as it ends up doubling
the number of DATEADDs of months:
SELECT DATEADD(s,-1,(DATEADD(m,1,DATEADD(m,
DATEDIFF(m, 0, GetDate()), 0))))
So we would simplify these and end up with the same
code as we had already derived above from First Date of Next Month
Number of days completed in the
current month
A quick way to do this is to use DATEPART() and retrieve the day part of
the date
SELECT DATEPART(d, GetDate())
This works ok for situations when it is just number
of days elapsed within one month.
A more generic approach is to calculate the number
of days using DATEDIFF()
DATEDIFF(d,
<Reference Start Date>, <Reference As At Date>)
This method would allow us to calculate to any
starting point (such as a quarter or financial year). But it needs the
adjustment of adding one day to compensate for the fact that when using
DATETIME the reference date (e.g. today) will never be complete
DATEDIFF(d,
<Reference Start Date>, <Reference As At Date>)+1
So using this method the following is another
method to calculate the number of days elapsed within the current month.
SELECT DATEDIFF(d, DATEADD(m, DATEDIFF(m, 0, GetDate()),
0),GetDate())+1
Number of days remaining in the
current month
This can be calculated using a variation of the previous method, in generic form:
DATEDIFF(d, <Reference As At Date>, <Reference End Date>)
This can be set out as follows to give days
remaining in the current month
SELECT DATEDIFF(d,Getdate(), DATEADD(s,-1,(DATEADD(m, DATEDIFF(m, 0, GetDate())+1,
0))))
Start date of the current quarter
This can be calculated using the same method used above for calculating Start Date of Current Month. The only change is the time interval (set to q for quarter instead of m for month)
SELECT DATEADD(q, DATEDIFF(q, 0, GetDate()), 0)
End date of the current quarter
This can be calculated using the same method used
above for calculating End Date of Current Month. The only change is the time
interval (set to q for quarter instead of m for month)
SELECT DATEADD(s,-1,(DATEADD(q, DATEDIFF(q, 0, GetDate())+1, 0)))
SELECT DATEADD(s,-1,(DATEADD(q, DATEDIFF(q, 0, GetDate())+1, 0)))
Number of days completed in the current quarter
This can be calculated using the method
set out above adjusted for quarters
DATEDIFF(d, <Reference Start Date>, <Reference As
At Date>)
SELECT DATEDIFF(d, DATEADD(q, DATEDIFF(q, 0, GetDate()), 0),GetDate())+1
Number of days remaining in the current quarter
This can also be calculated using the
method set out above for months, adjusted for quarters
DATEDIFF(d, <Reference As At Date>,
<Reference End Date>)
SELECT DATEDIFF(d,Getdate(), DATEADD(s,-1,(DATEADD(q, DATEDIFF(q, 0, GetDate())+1,
0))))
Start date of the current financial year
The start of the financial year in any analyses I have had to do so far has always been the 1st April. Things can be more complicated if the financial year is taken to start on 5th April or from a different month. Ignoring these possible complications for now, for me finding the start of the financial year simply means rolling back to the most recent previous 1st April from any given reference date.
The method I have devised uses DATEPART() to find the month number for the
reference (current) month.The difference between this and 4 (the month number
for April) is then the number of months to roll back.
There is a complication though. For months prior to
April, this method would create a negative number with the result the date
would roll forward not back.
So we need to test for whether the month number of
our reference month is less than 4 and if so use a modified calculation in
which we deduct an additional 12 months.
I have done this using a case statement
DATEADD(m,
(CASE WHEN DATEPART(m,<Reference
As At Date>)<4 THEN 4-DATEPART(m,<Reference
As At Date>)-12
ELSE 4-DATEPART(m,<Reference
As At Date>) END)
,<Reference
Month Start Date>)
This logic is not complicated, but it does look
complicated when developed into a single standalone select statement
SELECT DATEADD(m,
(CASE WHEN DATEPART(m,GetDate())<4 THEN 4-DATEPART(m,GetDate())-12
ELSE 4-DATEPART(m,GetDate()) END)
,DATEADD(m, DATEDIFF(m, 0, GetDate()),
0))
A small simplification is possible by combining the
+4 and -12 in the first part to give -8
SELECT DATEADD(m,
(CASE WHEN DATEPART(m,GetDate())<4 THEN -DATEPART(m,GetDate())-8
ELSE 4-DATEPART(m,GetDate()) END)
,DATEADD(m, DATEDIFF(m, 0, GetDate()),
0))
Start date of the next financial year
The start date of the next financial year can be obtained by adding 12 months onto the calculation of start of current financial year
SELECT DATEADD(m,
(CASE WHEN DATEPART(m,GetDate())<4 THEN 4-DATEPART(m,GetDate())-12+12
ELSE 4-DATEPART(m,GetDate()) +12 END)
,DATEADD(m, DATEDIFF(m, 0, GetDate()),
0))
which simplifies slightly to
SELECT DATEADD(m,
(CASE WHEN DATEPART(m,GetDate())<4 THEN 4-DATEPART(m,GetDate())
ELSE 16-DATEPART(m,GetDate()) END)
,DATEADD(m, DATEDIFF(m, 0, GetDate()),
0))
End date of the current financial
year
The end date of the current financial year can be
calculated by deducting one second from the start of the next
financial year
SELECT DATEADD(s,-1,DATEADD(m,
(CASE WHEN DATEPART(m,GetDate())<4 THEN 4-DATEPART(m,GetDate())
ELSE 16-DATEPART(m,GetDate()) END)
,DATEADD(m, DATEDIFF(m, 0, GetDate()),
0)))
A script which calculates all of
these as memory variables
You can adapt the following script to meet
different requirements. Using variables makes the calculations a little easier
to follow. It also allows a generic 'As At Date' to be set.
-------------------------------------------------
-- Date calculations -- sample SQL code
-------------------------------------------------
---------------------------------------------
-- Declare all variables
---------------------------------------------
DECLARE @AsAtDate as DATETIME -- As At Date
DECLARE @MONCurrentStartDate as DATETIME -- Start Date for As At Date's Current Month
DECLARE @MONCurrentEndDate as DATETIME -- End Date for As At Date's Current Month
DECLARE @MONCurrentDaysCompleted as INT =0 -- Numbers of days completed in Current Month
DECLARE @MONCurrentDaysLeft as INT =0 -- Numbers of days remaining in Current Month
DECLARE @MONNextStartDate as DATETIME -- Start Date for Month following As At Date
DECLARE @QTRCurrentStartDate as DATETIME -- Start Date for As At Date's Current Quarter
DECLARE @QTRCurrentEndDate as DATETIME -- End Date for As At Date's Current Quarter
DECLARE @QTRCurrentDaysCompleted as INT =0 -- Numbers of days completed in Current Quarter
DECLARE @QTRCurrentDaysLeft as INT =0 -- Numbers of days remaining in Current Quarter
DECLARE @QTRNextStartDate as DATETIME -- Start Date for Quarter following As At Date
DECLARE @FYRCurrentStartDate as DATETIME -- Start Date for As At Date's Current Financial Year
DECLARE @FYRCurrentEndDate as DATETIME -- End Date for As At Date's Current Financial Year
DECLARE @FYRCurrentDaysCompleted as INT =0 -- Numbers of days completed in Current Financial Year
DECLARE @FYRCurrentDaysLeft as INT =0 -- Numbers of days remaining in Current Financial Year
DECLARE @FYRNextStartDate as DATETIME -- Start Date for Financial Year following As At Date
------------------------------------------------------------------------------
-- Set As At Date
------------------------------------------------------------------------------
--SET @AsAtDate = dateadd(d, -1,GETDATE()) -- Set As At Date to yesterday
SET @AsAtDate='1997-03-12' -- Set As At Date to any date to test
-- Date calculations -- sample SQL code
-------------------------------------------------
---------------------------------------------
-- Declare all variables
---------------------------------------------
DECLARE @AsAtDate as DATETIME -- As At Date
DECLARE @MONCurrentStartDate as DATETIME -- Start Date for As At Date's Current Month
DECLARE @MONCurrentEndDate as DATETIME -- End Date for As At Date's Current Month
DECLARE @MONCurrentDaysCompleted as INT =0 -- Numbers of days completed in Current Month
DECLARE @MONCurrentDaysLeft as INT =0 -- Numbers of days remaining in Current Month
DECLARE @MONNextStartDate as DATETIME -- Start Date for Month following As At Date
DECLARE @QTRCurrentStartDate as DATETIME -- Start Date for As At Date's Current Quarter
DECLARE @QTRCurrentEndDate as DATETIME -- End Date for As At Date's Current Quarter
DECLARE @QTRCurrentDaysCompleted as INT =0 -- Numbers of days completed in Current Quarter
DECLARE @QTRCurrentDaysLeft as INT =0 -- Numbers of days remaining in Current Quarter
DECLARE @QTRNextStartDate as DATETIME -- Start Date for Quarter following As At Date
DECLARE @FYRCurrentStartDate as DATETIME -- Start Date for As At Date's Current Financial Year
DECLARE @FYRCurrentEndDate as DATETIME -- End Date for As At Date's Current Financial Year
DECLARE @FYRCurrentDaysCompleted as INT =0 -- Numbers of days completed in Current Financial Year
DECLARE @FYRCurrentDaysLeft as INT =0 -- Numbers of days remaining in Current Financial Year
DECLARE @FYRNextStartDate as DATETIME -- Start Date for Financial Year following As At Date
------------------------------------------------------------------------------
-- Set As At Date
------------------------------------------------------------------------------
--SET @AsAtDate = dateadd(d, -1,GETDATE()) -- Set As At Date to yesterday
SET @AsAtDate='1997-03-12' -- Set As At Date to any date to test
----------------------------------------------------------------------------
-- Calculate Month variations (for As At Date)
----------------------------------------------------------------------------
SET @MONCurrentStartDate = DATEADD(m, DATEDIFF(m, 0, @AsAtDate), 0)
SET @MONCurrentEndDate=DATEADD(s,-1,(DATEADD(m,1,@MONCurrentStartDate)))
SET @MONCurrentDaysCompleted=DATEDIFF(d, @MONCurrentStartDate,@AsAtDate)+1
SET @MONCurrentDaysLeft=DATEDIFF(d,@AsAtDate, @MONCurrentEndDate)
SET @MONNextStartDate=DATEADD(m, DATEDIFF(m, 0, @AsAtDate)+1, 0)
-------------------------------------------------------------------------------
-- Calculate Quarter variations (for As At Date)
-------------------------------------------------------------------------------
SET @QTRCurrentStartDate = DATEADD(q, DATEDIFF(q, 0, @AsAtDate), 0)
SET @QTRCurrentEndDate=dateadd(s,-1,(dateadd(q,1,@QTRCurrentStartDate)))
SET @QTRNextStartDate= DATEADD(q, DATEDIFF(q, 0, @AsAtDate)+1, 0)
SET @QTRCurrentDaysCompleted=datediff(d, @QTRCurrentStartDate,@AsAtDate)+1
SET @QTRCurrentDaysLeft=DATEDIFF(d,@AsAtDate, @QTRCurrentEndDate)
-- Calculate Month variations (for As At Date)
----------------------------------------------------------------------------
SET @MONCurrentStartDate = DATEADD(m, DATEDIFF(m, 0, @AsAtDate), 0)
SET @MONCurrentEndDate=DATEADD(s,-1,(DATEADD(m,1,@MONCurrentStartDate)))
SET @MONCurrentDaysCompleted=DATEDIFF(d, @MONCurrentStartDate,@AsAtDate)+1
SET @MONCurrentDaysLeft=DATEDIFF(d,@AsAtDate, @MONCurrentEndDate)
SET @MONNextStartDate=DATEADD(m, DATEDIFF(m, 0, @AsAtDate)+1, 0)
-------------------------------------------------------------------------------
-- Calculate Quarter variations (for As At Date)
-------------------------------------------------------------------------------
SET @QTRCurrentStartDate = DATEADD(q, DATEDIFF(q, 0, @AsAtDate), 0)
SET @QTRCurrentEndDate=dateadd(s,-1,(dateadd(q,1,@QTRCurrentStartDate)))
SET @QTRNextStartDate= DATEADD(q, DATEDIFF(q, 0, @AsAtDate)+1, 0)
SET @QTRCurrentDaysCompleted=datediff(d, @QTRCurrentStartDate,@AsAtDate)+1
SET @QTRCurrentDaysLeft=DATEDIFF(d,@AsAtDate, @QTRCurrentEndDate)
-------------------------------------------------------------------------------
-- Calculate Financial Year variations (for As At Date)
-------------------------------------------------------------------------------
SET @FYRCurrentStartDate=
DATEADD(m, (case when datepart(m,@AsAtDate)<4 then 4-datepart(m,@AsAtDate)-12 --adjust for Jan to Mar to count back
else 4-DATEPART(m,@AsAtDate) end),@MONCurrentStartDate)
SET @FYRNextStartDate=
DATEADD(m,(CASE WHEN DATEPART(m,@AsAtDate)<4 THEN 4-DATEPART(m,@AsAtDate)
ELSE 16-DATEPART(m,@AsAtDate) END),@MONCurrentStartDate)
SET @FYRCurrentEndDate=DATEADD(s,-1,(DATEADD(yy,1,@FYRCurrentStartDate)))
SET @FYRCurrentDaysCompleted=DATEDIFF(d, @FYRCurrentStartDate,@AsAtDate)+1
SET @FYRCurrentDaysLeft=DATEDIFF(d,@AsAtDate, @FYRCurrentEndDate)
---------------------
-- Display results
--------------------
SELECT @AsAtDate as [As At],
@MONCurrentStartDate as [Current Month Start],
@MONCurrentEndDate as [Current Month End],
@MONCurrentDaysCompleted as [Current Month Days Completed],
@MONCurrentDaysLeft as [Current Month Days Left],
@MONNextStartDate as [Next Month Start]
SELECT @AsAtDate as [As At],
@QTRCurrentStartDate as [Current Quarter Start],
@QTRCurrentEndDate as [Current Quarter End],
@QTRCurrentDaysCompleted as [Current Quarter Days Completed],
@QTRCurrentDaysLeft as [Current Quarter Days Left],
@QTRNextStartDate as [Next Quarter Start]
SELECT @AsAtDate as [As At],
@FYRCurrentStartDate as [Current Fin Year Start],
@FYRCurrentEndDate as [Current Fin Year End],
@FYRCurrentDaysCompleted as [Current Fin Year Days Completed],
@FYRCurrentDaysLeft as [Current Fin Year Days Left],
@FYRNextStartDate as [Next Fin Year Start]
You can run the script above to test the results of the calculations. In practice you will only need to use some elements of it. But if you keep the overall script you can dip into it and lift the elements you need for any new SQL script.
User-defined Functions for handling
relative dates
If you use date calculations such as these a lot, it may be worth creating some User Defined Functions. You could create individual functions for each measure. Or you could create a single function which combines them all. The following code is one version of a multi-purpose function. As input it takes the 'As At Date' and a thee letter code indicating what date to return
If you use date calculations such as these a lot, it may be worth creating some User Defined Functions. You could create individual functions for each measure. Or you could create a single function which combines them all. The following code is one version of a multi-purpose function. As input it takes the 'As At Date' and a thee letter code indicating what date to return
---------------------------------------------------------------------------------------
-- Function RelativeDate ()
---------------------------------------------------------------------------------------
-- E.Bolton. Last updated 26/12/2017
---------------------------------------------------------------------------------------
-- Takes any given date @AsAtDate
-- and a three-character return type
code @ReturnCode
-- which indicates what to return
-- Returns the relative date
-- Inputvalues for @ReturnCode
-- MCS
indicates return Start Date for As At Date's Current Month
-- MCE
indicates return End Date for As At Date's Current Month
-- MNS
indicates return Start Date for Month following As At Date
-- QCS
indicates return Start Date for As At Date's Current Quarter
-- QCE
indicates return End Date for As At Date's Current Quarter
-- QNS
indicates return Start Date for Quarter following As At Date
-- FYS
indicates return Start Date for As At Date's Current Financial Year
-- FYE indicates return End Date for As At Date's
Current Financial Year
-- FYN
indicates return Start Date for Financial Year following As At Date
---------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[RelativeDate]
--alter FUNCTION
[dbo].[RelativeDate]
( @AsAtDate datetime,
@ReturnCode Varchar(3))
RETURNS DATETIME
AS
BEGIN
DECLARE @ReturnDate as DATETIME
DECLARE @MONCurrentStartDate as DATETIME -- Start Date for As At Date's Current Month
MCS
DECLARE @MONCurrentEndDate as DATETIME -- End Date for As At Date's Current Month
MCE
DECLARE @MONNextStartDate as DATETIME -- Start Date for Month following As At Date MNS
DECLARE @QTRCurrentStartDate as DATETIME --
Start Date for As At Date's Current Quarter QCS
DECLARE @QTRCurrentEndDate as DATETIME -- End Date for As At Date's Current Quarter QCE
DECLARE @QTRNextStartDate as DATETIME -- Start Date for Quarter following As At Date QNS
DECLARE @FYRCurrentStartDate as DATETIME -- Start Date for As At Date's Current Financial
Year FYS
DECLARE @FYRCurrentEndDate as DATETIME -- End Date for As At Date's Current Financial Year
FYE
DECLARE @FYRNextStartDate as DATETIME -- Start Date for Financial Year following As At
Date FYN
-----------------------------
-- Date Calculations
-----------------------------
SET @MONCurrentStartDate
=DATEADD(m, DATEDIFF(m, 0, @AsAtDate), 0)
SET @MONCurrentEndDate=DATEADD(s,-1,(DATEADD(m,1,@MONCurrentStartDate)))
SET @MONNextStartDate=DATEADD(m, DATEDIFF(m, 0, @AsAtDate)+1, 0)
SET @QTRCurrentStartDate
= DATEADD(q, DATEDIFF(q, 0, @AsAtDate), 0)
SET @QTRCurrentEndDate=DATEADD(s,-1,(DATEADD(q,1,@QTRCurrentStartDate)))
SET @QTRNextStartDate= DATEADD(q, DATEDIFF(q, 0, @AsAtDate)+1, 0)
SET @FYRCurrentStartDate=
DATEADD(m, (case when DATEPART(m,@AsAtDate)<4 then 4-DATEPART(m,@AsAtDate)-12 --adjust for Jan to
Mar to count back
else 4-DATEPART(m,@AsAtDate) end),@MONCurrentStartDate)
SET @FYRCurrentEndDate=DATEADD(s,-1,(dateadd(yy,1,@FYRCurrentStartDate)))
SET @FYRNextStartDate=
DATEADD(m,(CASE
WHEN DATEPART(m,@AsAtDate)<4
THEN 4-DATEPART(m,@AsAtDate)
ELSE 16-DATEPART(m,@AsAtDate) END),@MONCurrentStartDate)
--------------------------------------------------
-- Set @ReturnDate based on input
code @ReturnCode
--------------------------------------------------
SET @ReturnDate
=
(Case when @ReturnCode='MCS' then
@MONCurrentStartDate
when @ReturnCode='MCE' then
@MONCurrentEndDate
when @ReturnCode='MNS' then
@MONNextStartDate
when @ReturnCode='QCS' then
@QTRCurrentStartDate
when @ReturnCode='QCE' then
@QTRCurrentEndDate
when @ReturnCode='QNS' then
@QTRNextStartDate
when @ReturnCode='FYS' then
@FYRCurrentStartDate
when @ReturnCode='FYE' then
@FYRCurrentEndDate
when @ReturnCode='FYN' then
@FYRNextStartDate
end)
RETURN @ReturnDate
END
GO
There are various ways that the
function above could have been constructed. I have chosen to calculate all the
variations and then return the required one. It could be argued that this
expends unnecessary resources each time. Alternative versions could be designed
with more careful conditional logic. But the code above works ok and is not
slow. And some of the dates calculated are used to calculate some of the others
Choices
You can use single, self-contained
expressions which calculate the values whenever you need them. This has the
advantage of completeness and portability. And when debugging, the source of a
problem is easier to locate. But you will end up writing fairly long
expressions which are hard to follow and which can get messed up easily
You may find that calculating the
values and retaining these in memory variables makes things easier to manage.
This may work well if you need to use the same values several times in the SQL
script. But in simple scripts, it probably over-complicates things.
You could create a parameter driven
User defined Function (along the lines of the one illustrated here) and then
call it from all your subsequent scripts. This has the advantage of economy but
you will need to ensure that the function has been set up on any database
instances on which you want to run it
You could create individual User
defined Functions for each type of calculation. Apart from the initial headache
of doing this, it will provide a useful palette of options.
As with all these things, inevitably
any of this could be done better (more efficiently, made more compact, made
more generic, use some ingenious algorithm that will enhance your reputation)
How far you go in tuning and fine
polishing SQL scripts is up to you.
I have had to overcome a tendency
towards the paralysis of perfectionism by using a 'fit for purpose test'. It
goes something like this:
Question 1: Does it work? If Yes move on to Question 2 Question 2: Does it take forever to run (or too long for its context)? If No move on the Question 3 Question 3: Does it crash the server, lock up any key applications, or dim the lights in the building? If No then move on to Question 4 Question 4: I have a limited amount of lifetime resource. Do I want to spend some of it making this thing that already works ok work even better? Or do I want to use it to solve one of the many other problems I still need to deal with? Move on to the next challenge. |
Before you go
You might want to check out the following
No comments:
Post a Comment