Following query will run respective to today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month. Let us see how we can do this with the help of Date Function in SQL Server.
DECLARE @mydate DATETIME SELECT @mydate = GETDATE() SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101), 'Last Day of Previous Month' UNION SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value, 'First Day of Current Month' AS Date_Type UNION SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type UNION SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))), DATEADD(mm,1,@mydate)),101), 'Last Day of Current Month' UNION SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1), DATEADD(mm,1,@mydate)),101), 'First Day of Next Month' GO
Here are few additional blog posts on this subject:
- Adding Datetime and Time Values Using Variables
- Find Current System Date, Time and Time Offset
- Get Time in Hour, Minute Format from a Datetime – Get Date Part Only from Datetime
I have seen scores of expert developers getting perplexed with SQL Server in finding time only from datetime datatype. Let us have a quick glance look at the solution. Let us learn about how to get Time in Hour:Minute Format from a Datetime as well as to get Date Part Only from Datetime.
Reference: Pinal Dave (https://blog.sqlauthority.com)
188 Comments. Leave new
If SQL Server was a country, you’d be on that country’s Mt. Rushmore. If SQL Server was a NFL team, you’d be the franchise player. Who’s the man? Pinal Dave is the man.
You are very kind!
Awesome….i found each n every solution of my SQL question from your blog..salute to you…
I need to run a report on monthly basis.
My requirement is when ever i run the report ,the query should take first and last date of previous month using sysdate.Can Some one help me on this
Hi Pinal ,
I think its good solutions but can I suggest not converting to varchar as depending on what people do with the code after it could be wrong. ie there using an Australian Date format vs US date format etc
I modded the solution for the first day of the month
–first day of month
–with time zeroed out
SELECT CAST(DATEADD(DAY,-DAY(getdate())+1, CAST(GETDATE() as date)) as datetime)
–with time as it was
SELECT DATEADD(DAY,-DAY(getdate())+1, CAST(GETDATE() as datetime))
Would just need to replace getdate() with the whatever date they want to pass in.
P.S. love your blog. Its been really helpful
Thanks for sharing knowledge
Thanks Dave
Thanks. Past few months I was using this code, but today..
I tried to get the first day of the 4th month, where current day is in October. Instead of giving me ‘2/1/2014’, it gave me ‘1/30/2014’.
I meant the year ‘2015’
sir can u explain easily way please
easiest way is https://docs.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql?view=sql-server-2017
Thank you!
Your welcome Rulovich.
I would use following method to avoid any typecasting
SELECT DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0) AS FirstOfTheMonth, DateAdd(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, getdate()) + 1 , 0) AS LastDateOfMonth
For Latest SQL server SELECT EOMONTH(GETDATE()) will return last day of month.
EOMONTH was not there in earlier versions of SQL.
SELECT DATEDIFF(yy, BirthDate, GETDATE()) – CASE
WHEN MONTH(BirthDate) > MONTH(GETDATE())
OR (MONTH(BirthDate) = MONTH(GETDATE())
AND DAY(BirthDate) > DAY(GETDATE()))
THEN 1
ELSE 0
END AS Age;
DECLARE @PreviousMonthStart DATETIME
DECLARE @PreviousMonthEnd DATETIME
SET @PreviousMonthStart = DATEADD(m,DATEDIFF(m,0,GETDATE())-1,0)
SET @PreviousMonthEnd = DATEADD(ms,-2,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
PRINT @PreviousMonthStart
PRINT @PreviousMonthEnd
SELECT * FROM MyTable
WHERE MyDate >= @PreviousMonthStart
AND MyDate < @PreviousMonthEnd
Experts,
I am not sure if this blog page is active, but I have a scenario I need help with.
I want to populate 1 year at a time the First_date_of _Month and Last_date_of_Month into a table.
Something like this:
Year | First_Date_of_Month|Last_date_of_Month
2013|01-01-2013|01-31-2013
2013|02-01-2013|02-28-2013 and so on…Help!
need 2016-02-02 .this format .how can i do it using abouv query
Great, small change in the script. Don’t required to add a month for getting a day for “Last Day of Current Month” and “First Day of Next Month”.
Modified script below:
DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101),’Last Day of Previous Month’
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,’First Day of Current Month’ AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, ‘Today’ AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),DATEADD(mm,1,@mydate)),101),’Last Day of Current Month’
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),DATEADD(mm,1,@mydate)),101),’First Day of Next Month’
GO