Few questions are always popular. They keep on coming up through email, comments or from co-workers. Finding Last Day of Any Month is similar question. I have received it many times and I enjoy answering it as well.
I have answered this question twice before here:
SQL SERVER – Script/Function to Find Last Day of Month
SQL SERVER – Query to Find First and Last Day of Current Month
Today, we will see the same solution again. Please use the method you find appropriate to your requirement.
Following script demonstrates the script to find last day of previous, current and next month.
----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth
ResultSet:
LastDay_PreviousMonth
———————–
2007-07-31 23:59:59.000
LastDay_CurrentMonth
———————–
2007-08-31 23:59:59.000
LastDay_NextMonth
———————–
2007-09-30 23:59:59.000
If you want to find last day of month of any day specified use following script.
--Last Day of Any Month and Year
DECLARE @dtDate DATETIME
SET @dtDate = '8/18/2007'
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
LastDay_AnyMonth
ResultSet:
LastDay_AnyMonth
———————–
2007-08-31 23:59:59.000
Reference : Pinal Dave (https://blog.sqlauthority.com)
289 Comments. Leave new
Hi,
I see function are cool, but have a bug i think,
2007-08-31 23:59:59.000
more usefull i think can be value
2007-08-31 23:59:59.999
any idea how to create such time value?
Hi
thanks Pinal dave for solution
Mr evisoft
you can use here
select dateadd(d,datediff(d,0,dateadd(s,-1,dateadd(m,datediff(m,0,getdate())+1,0))),0)
try it
create procedure za1
—- drop procedure za1
@var1 as datetime
as
select @var1-day(@var1)
———– Execution —————
exec za1 ‘2007-09-30’
Hi –
What about 1st day of previous month?
I can not find it anywhere
Thanks!
ok don’t worry about the problem.
Copy the below code in your sql:
—————————————————–
declare @date datetime
set @date = ’09/25/2010′
select DATEADD(DD, -DAY(DATEADD(DD, -DAY(@date),@date))+1,DATEADD(DD, -DAY(@date),@date))
hi
it is very nice. But i can’t understand DATEDIFF(m,0,getdate()). It returns 1292. Is it month of starting date what date is supported by sqlserver.
It is the difference in months between 1900-01-01 to today
polina,
you just have to tweek the script thats posted a little…. checck it out.
SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)) FirstDay_PreviousMonth
SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate()),0)) FirstDay_CurrentMonth
SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) FirstDay_NextMonth
hiee i am new to sql server..
can u tel me d logic of finding first/last day of the month using these queries???
I tried a lot but didn’t get it!!!!!!
>> avsrao,
The reason why you get 1292 is because it gets you the value of the number of months from 1900 (since sql server start date will be from 1900) to the current date.
Assuming month as SEPT.
if you add ( 12*107 + 9 – (first month of 1900)) = 1292
( 12*107 + 9 – (first month of 1900)) = 1292
can u please let me know why we multiply 12 by 107
Hey i got it ,it is the difference of current year and 1900
Sunny,
Why have you used 2nd DATEADDs in your query of 1st day of month? The 1st one is sufficient.
SELECT DATEADD(mm, DATEDIFF(m,0,getdate())-1,0) FirstDay_PreviousMonth
SELECT DATEADD(mm, DATEDIFF(m,0,getdate()),0) FirstDay_CurrentMonth
SELECT DATEADD(mm, DATEDIFF(m,0,getdate())+1,0) FirstDay_NextMonth
Can you please tell me how to get the first and the last day of the previous month for the following sql in mm/dd/yyyy hh:mm:ss AM format?
E.g: 09/01/2007 12:47:00 PM and 09/30/2000 5:41:00 PM
SELECT DATEADD(mm, DATEDIFF(m,0,getdate())-1,0) FirstDay_PreviousMonth
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0)) LastDay_PreviousMonth
You can use CONVERT function with style 101. However the format should be done in a front end application
its cool
Hi,
I want to suggest another way to calculate last day of the month:
DECLARE @Month nvarchar(2)
DECLARE @Year nvarchar(4)
SET @Month = ‘2’
SET @Year = ‘1857’
Select DateAdd(d, -1, DateAdd(m, 1, @Year+ ‘-‘ + (@Month) + ‘-1 23:59:59.998’))
You should not reply on the millisecond part. From datetime to datetime2 the value differs and you may get different results
How to I select the day before yesterday date records
WHERE
datecol>=dateadd(day,datediff(day,0,getdate())-2,0) and
datecol<dateadd(day,datediff(day,0,getdate())-1,0)
i am creating cyrstal reports i want divide month and also concentrate on leap year
—————————————————————————-
Period user Time Spent
—————————————————————————-
jan 01-jan 15 christopher 40
jan 01-jan 15 james 67
jan 16-jan 31 christopher 55
feb 01-feb 15 james 56
feb 16-jab 28 james 56
like that i want report but i dont know how split month
please tell
Hi Pinal Dave
Ur site is very useful for me … i m new to sql server
recently i have faced 1 interview ,in that they asked some questions which i dont knw
please provide me the answer for the following
1 “If one table is locked in front-end , how can we remove the lock from back-end”?
2 max how many @@ error and raise error we can use?
3 max how many .ndf files we can create at the time of creation of a database?
4 how do we know an index is properly used or not?
5 max how many .ndf,.ldf,.mdf files we can create?
You the man, Dave! You really helped me out a bunch with your time queries! Thanks!
Mark
declare @lastday datetime
declare @inMonth datetime
set @inMonth = ‘2/1/2008’
select @lastday = dateadd(day,-1,dateadd(month,1,@inMonth))
select convert(datetime,@lastday,102) as ‘LastDay’
This can be simplified to
select dateadd(month,datediff(month,0,@inMonth)+1,-1)
Dear Sir / Madam,
I want full date time functions and using methods and useful magazines for SQL server 2000
Read about dateadd, datediff functions. You can find them in Books On Line
You are awesome!! Thanks a lot.
dear sir,
your artical is excellent
select datediff(mm,0,getdate()) . It gives 1301. what is this value. what data type datediff gives
Number of months between 1900-01-01 and today
The datatype is integer
hello sir,
how can I add time to the current system time
select getdate()+’12:46:23′