Menu
I am trying to get the first day of last month like 01/01/2013, also i want to get the last day of previous month like 01/31/2013. If we are in March then i want to do the same thing like 02/01/2013 and 02/28/2013 and so on.... thanks
549
To get the previous month in SQL Server, subtract one month from today's date and then extract the month from the date. First, use CURRENT_TIMESTAMP to get today's date. Then, subtract 1 month from the current date using the DATEADD function: use MONTH as the date part with -1 as the parameter.
For the last day of the previous month: SELECT EOMONTH(DATEADD(MONTH,-1,GETDATE())); For the last day of the previous month formatted as needed: SELECT CONVERT(VARCHAR(10), EOMONTH(DATEADD(MONTH,-1,GETDATE())), 101);
To find the first day, the date time is first converted to date only using TO_DATE function. Then, the date is changed to the first day of the month by using the DATE_FROM_PARTS function. This works by inputting 01' in the 'day' part of the function.
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.
This should do it:
--First day of last month
SELECT DATEADD(m,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0))
--Last day of last month
SELECT DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
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
