Today, I had to create an ETL script for current’s month data loading into the data warehouse. In my script, I had to get the first and last day of the current month.
I’m going to demonstrate two approaches. First approach is for Microsoft SQL Server 2012 and newer with EOMONTH function and second for earlier releases with DATEADD function.
Pre SQL Server 2012 approach with DATEADD:
--Today's date is 24th July 2012 DECLARE @today AS DATE = '2012-08-24'; --First day of month SELECT DATEADD(dd, -DAY(@today) + 1, @today); --Last day of month SELECT DATEADD(dd, -DAY(DATEADD(mm, 1, @today)), DATEADD(mm, 1, @today));
Command decomposition follows. Bellow example is assuming today’s date is 24th July.
First day of a month
-DAY(@today) + 1
You get day of a month with DAY function [24], make it negative [-24] and add 1 [-23]. Use DATEADD function for the date’s subtraction.
DATEADD(dd, -23, @today) -- => 24 – 23 = 1st July
Last day of a month
Add 1 month to the current date, get the current day of month [24] and make it negative [-24]
-DAY(DATEADD(mm, 1, @today))
What we get is
DATEADD(dd, -24, DATEADD(mm, 1, @today))
Add 1 month to current date [24th August]
DATEADD(mm, 1, @today)
We get DATEADD(dd, -24, 2012-08-24 )) => 31st July
DATEADD(dd, -24, 2012-08-24 ))// => 31st July
SQL 2012 approach with EOMONTH and little DATEADD
--Today's date is 24th July DECLARE @today AS DATE = SYSDATETIME(); --First day of a month SELECT DATEADD(dd, -DAY(@today) + 1, @today); --or in another way SELECT DATEADD(dd, 1, EOMONTH(@today, -1)); --Last day of a month SELECT EOMONTH(@today);
First day of a month
EOMONTH function accepts two arguments. First the reference date, second how many months to add to the reference date. We take advantage of this behaviour by getting the last day of previous month [30st June].
EOMONTH(@today, -1)
By adding 1 day with DATEADD function we get 1st July.
DATEADD(dd, 1, 2012-07-31)
Last day of month
It couldn’t be simpler 🙂
Conclusion
To save some work in the future you can create your own function. Hereafter follows my own function:
SET ANSI_NULLS ON -- ============================================= -- Author: Jiri Hubacek -- Create date: 24th July 2012 -- Description: Function returns first day of month [date] -- ============================================= CREATE FUNCTION FirstDayOfMonth ( @dateIN DATE ) RETURNS DATE AS BEGIN DECLARE @Result DATE; SELECT @Result = DATEADD(dd, -DAY(@dateIN) + 1, @dateIN); RETURN @Result; END;
And call it in your query:
SELECT dbo.FirstDayOfMonth (SYSDATETIME());
Have a nice weekend!
Leave a Reply