How to find the first day of a month and the last day of a month?
In your sql scripts you have probably needed to find the first day of a month.
Also you probably needed to find the last day of the same month.
For my case I had to process a group of records whose some datetime column
value is between the beginning date of some month and the last day of the same
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 sql function which will be used for the Get First Day Of
Month sql user defined function we will create.
Assume that the datetime parameter is @datetimeparam is equal to some date
value or may be equal to today by setting its value to GetDate().
DATEDIFF(MM, 0, @datetimeparam)
Returns us 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 we can create the GetFirstDayOfMonth function with this information.
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 function by running the below sql udf call.
SELECT dbo.GetFirstDayOfMonth( GetDate() )
The second part of the question is how to find the end of a month. So we can
add one more month to find the beginning of the next month.
DATEADD(MM, DATEDIFF(MM, 0, @datetimeparam) + 1, 0)
And then substract the minimum amount of time from that newly calculated date
value. 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))
Outputs 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
SELECT @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() )
You can download the create scripts for the sql server user defined functions
GetFirstDayOfMonth and GetLastDayOfMonth from
http://www.kodyaz.com/files/21/sql_server_2000_user_defined_functions/entry335.aspx