I have a stored procedure that has to accept a month as int (1-12) and a year as int. Given those two values, I have to determine the date range of that month. So I need a datetime variable to represent the first day of that month, and another datetime variable to represent the last day of that month. Is there a fairly easy way to get this info?
To use the dateadd function in SQL, we need to use the SELECT query followed by the DATEDIFF function and then return the output. The datePart should be provided only valid input (Check valid input table for datePart) and the startDate and the endDate should be provided with a date input in a proper format.
Below are the functions with logic explanation: 1. First day of current month: select DATEADD(mm, DATEDIFF(m,0,GETDATE()),0): in this we have taken out the difference between the months from 0 to current date and then add the difference in 0 this will return the first day of current month.
The LAST_DAY() function extracts the last day of the month for a given date.
First day of the month: SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
Last day of the month: SELECT DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))
Substitute a DateTime variable value for GETDATE().
I got that long ago from this very handy page which has a whole bunch of other date calculations, such as "Monday of the current week" and "first Monday of the month".
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With