How to find the first day of month and sql last day of month ?
SQL Server developers frequently require to find sql first day of month in sql scripts that they use in their applications.
Developers also need to find the sql last day of month in SQL Server using a sql function.
With SQL Server 2011, Denali CTP3 release, a new SQL Server datetime function EOMonth() is introduced to T-SQL developers to calculate the last day of month in SQL Server. If you are using a database with compability level 110 or higher please refer to SQL tutorial Calculate SQL Last Day of Month using End of Month EOMonth() Function in SQL Server 2011 for sql code samples. If your database is SQL Server 2008 R2 or pevious versions, than the solution given in this tutorial will help you for end of month in SQL calculation.
For my case I had to process a group of database records whose some datetime column value is between the beginning date of some month (first day of month) and the last day of month.
For example, we will select all the shipments where the shipment date is later than the beginning of the month same with some certain datetime parameter belongs to. And the shipment date is earlier than the end of current month for reporting purposes.
So to define the limits of the date range for valid shipment record for the report, we have to define the beginning of a month and end of a month.
DATEDIFF is the base datetime sql function which will be used for the get first day of month SQL Server user defined function we will create.
Assume that the datetime parameter is @datetimeparam and it is equal to some date value or may be equal to today by setting its value to GetDate().
DATEDIFF(MM, 0, @datetimeparam)
Above sql datediff function returns t-sql developers the number of months there are since the base date that is defined in SQL Server, January 1st, 1900. Base date is the system reference date for SQL Server. So taking January 1st 1900 as the reference date by setting the startdate argument of the DATEDIFF function to 0, DATEDIFF(MM, 0, @datetimeparam()) calculates the number of months since then.
If you add the amount of months to reference date, you will get the beginning date of the month of datetimeparam.
SELECT DATEADD(MM, DATEDIFF(MM, 0, @datetimeparam), 0)
So sql developers can create the GetFirstDayOfMonth function in order to calculate first day of the month in SQL Server scripts.
CREATE FUNCTION GetFirstDayOfMonth (
@InDate Datetime
) RETURNS Datetime
AS
BEGIN
DECLARE @OutDate Datetime
SELECT @OutDate = DATEADD(MM, DATEDIFF(MM, 0, @InDate), 0)
RETURN @OutDate
END
You can try the SQL function by running the below sql udf (User Defined Function) call.
Below sql get first day of month we are in at current time.
SELECT dbo.GetFirstDayOfMonth( GetDate() )
The second part of the question is how to find the end of a month. T-SQL developers have to get last day of month using a similar SQL Server function.
So developers can add one more month to find the beginning of the next month.
This time the required sql function here is sql dateadd function.
DATEADD(MM, DATEDIFF(MM, 0, @datetimeparam) + 1, 0)
And then substract the minimum amount of time from that newly calculated date value which is the first day of month following.
We can say that minimum amount is 1 ms which will be a false assumption.
For example, if we try to calculate the end of the current month by using the below script and substracting 1 ms
SELECT DATEADD(MS, -1, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0))
will return
2006-06-01 00:00:00.000
This is because, SQL Server has minimum a 3 millisecond period between two adjacent datetime values that it can store.
You can run and see the outputs from the below select scripts
SELECT DATEADD(MS, -1, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0))
SELECT DATEADD(MS, -2, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0))
SELECT DATEADD(MS, -3, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0))
SELECT DATEADD(MS, -4, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0))
SELECT DATEADD(MS, -5, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0))
SELECT DATEADD(MS, -6, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0))
SELECT DATEADD(MS, -7, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0))
SELECT DATEADD(MS, -8, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0))
SELECT DATEADD(MS, -9, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0))
SELECT DATEADD(MS, -10, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0))
The outputs of the above sql dateadd functions are as:
2006-06-01 00:00:00.000
2006-05-31 23:59:59.997
2006-05-31 23:59:59.997
2006-05-31 23:59:59.997
2006-05-31 23:59:59.993
2006-05-31 23:59:59.993
2006-05-31 23:59:59.993
2006-05-31 23:59:59.993
2006-05-31 23:59:59.990
2006-05-31 23:59:59.990
If we return to our original problem, we will continue for the solution as substracting 3 milliseconds from the calculated beginning date of the next month.
SELECT DATEADD(MS, -3, DATEADD(MM, DATEDIFF(MM,0,@datetimeparam) + 1,0))
So we can create the GetLastDayOfMonth function by using the following script.
CREATE FUNCTION GetLastDayOfMonth (
@InDate Datetime
) RETURNS Datetime
AS
BEGIN
DECLARE @OutDate Datetime
SET @OutDate = DATEADD(MS, -3, DATEADD(MM, DATEDIFF(MM,0,@InDate) + 1,0))
RETURN @OutDate
END
And you can try the function by running the below sql udf call.
SELECT dbo.GetLastDayOfMonth( GetDate() )
I hope SQL developers and SQL administrators will find the above two sql function useful to get first day of month and to get last day of month in SQL Server t-sql scripts.
You can download the create scripts for the sql server user defined functions GetFirstDayOfMonth and GetLastDayOfMonth from SQL Server 2000 User Defined Functions