SQL SERVER - Find Last Day of Any Month - Current Previous Next « Journey to SQL Authority with Pinal Dave

SQL SERVER - Find Last Day of Any Month - Current Previous Next

August 18, 2007 by pinaldave

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 (http://www.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Documentation, SQL Download, SQL Error Messages, SQL Function, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, SQL Utility, T SQL, Technology | 35 Comments

35 Responses to “SQL SERVER - Find Last Day of Any Month - Current Previous Next”

on August 30, 2007 at 12:47 am evisoft

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?
on September 13, 2007 at 1:05 am alok

create procedure za1
—- drop procedure za1
@var1 as datetime
as
select @var1-day(@var1)

———– Execution —————
exec za1 ‘2007-09-30′
on September 14, 2007 at 12:07 pm Polina

Hi -

What about 1st day of previous month?
I can not find it anywhere

Thanks!
on September 21, 2007 at 4:06 am avsrao

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.
on September 21, 2007 at 2:13 pm sunny

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
on October 5, 2007 at 1:48 pm sunny

>> 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
on October 11, 2007 at 5:47 am Sourav

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
on October 29, 2007 at 3:47 pm Parekh -Begineer

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
on November 2, 2007 at 1:00 pm karunaker reddy v

its cool
on November 30, 2007 at 1:03 am Arthur

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′))
on December 7, 2007 at 11:28 am Sathishkumar.K

How to I select the day before yesterday date records
on December 21, 2007 at 11:35 am christopher

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
on January 23, 2008 at 10:10 pm zak

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?
on March 24, 2008 at 9:27 am Shmoov

You the man, Dave! You really helped me out a bunch with your time queries! Thanks!

Mark
on March 27, 2008 at 9:28 pm DTMxxLLC

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’
on March 31, 2008 at 9:57 pm Sivakumar S

Dear Sir / Madam,

I want full date time functions and using methods and useful magazines for SQL server 2000
on May 23, 2008 at 11:51 pm Nila

You are awesome!! Thanks a lot.
on June 12, 2008 at 5:55 pm murali

dear sir,
your artical is excellent
on June 12, 2008 at 6:11 pm murali

select datediff(mm,0,getdate()) . It gives 1301. what is this value. what data type datediff gives
on June 12, 2008 at 6:29 pm murali

hello sir,
how can I add time to the current system time
on June 13, 2008 at 11:34 pm Katie

Pinal — what a great site you’ve got! I found my answer here quickly and clearly. I’ll be back often.

Your syntax gave me exactly what I needed in my query, but I’m not clear on what the s switch/function is in the statment ((DATEADD(s,-1,DATEADD) and I’d like to udnerstand it so I can apply it in the future.

Thanks!

Katie
on June 21, 2008 at 5:36 pm Haushila

Dear Murali,

datediff()- Returns the number of date and time boundaries crossed between two specified dates.

you have given datediff(mm,0,getdate())
‘0′ is nothing in datediff function. so it’s throwing an garbage value.

You have to give some date instead of 0.
datediff gives numeric data.
on July 17, 2008 at 2:02 pm ida

Hai Pinal

i have a problem here to get the last day of month..
let say i have table A that have field month and year only…

how to select this table based on 2 parameter (month and year) to get last day of that month and year…

thanks
on July 25, 2008 at 5:01 pm Nirmala

Sir,
I want how to search the information based on giving a parameter
on August 1, 2008 at 10:00 am catherine

Hi Dave,
I want to get datetime in form of yyyymmdd from stored proc. Can u guide me for this. Thanks in advance
on August 14, 2008 at 12:23 pm Karunaker Reddy

use ‘ like’ operator
on August 28, 2008 at 2:01 am Chadzuk

I may be asking a very stupid question however I need to turn the output of your above statement that finds the last day of a specific month into a “YYYY-MM-DD” format with no time and use in a stored procedure query. Trying to do a CONVERT(DATETIME, @variable, 102) doesn’t seem to be doing it. Maybe I’m not thinking clearly but this seems to have worked for everything else. Any ideas?
on August 28, 2008 at 2:12 am Chadzuk

Nevermind, I got it. I had to use a CONVERT(CHAR, @variable, 102) instead of DATETIME and it works perfect. Thanks for the information about the last day of each month.
on September 28, 2008 at 6:48 pm Rajesh Agnihotri

Hi
I am new in SQL query writing.
please help me in writing the query to convert today (system date and time) to EPOCH format seconds.

I would like to use this query along with my existing helpdesk tool that stores dates in EPOCH (10 digit integer format).

Regards
Rajesh Agnihotri
on October 23, 2008 at 5:13 pm Saurav

How to find the last date of given year’s month.
on November 29, 2008 at 6:00 pm Malar

Can anybody help me how to find first,second,third,fourth last mon,tue,wed,thur,fri,sat,sun of a month(In datetime or date format.).I hav a program scheduled to be run like this,I need to find next run date .
Thanks……..
on November 30, 2008 at 1:58 pm Malar

how to find last monday of month? any ready functions please……….

Thanks……….
on December 5, 2008 at 4:14 am Ken

Last Day of Previous month I always used:

dateadd(dd, -Day(GetDate()), GetDate())

or (to strip off the time component):

convert(char(10), dateadd(dd, -Day(GetDate()), GetDate()), 101)

Is there any reason two calls to GetDate() would be an issue here? I believe they will return the exact same time. If that is the case then this method seems a lot more intuitive, simply go back the number of days you are into the current month. If it is the 5th, go back 5 days which puts you on the last day of the previous month, regardless of how many days that month contains (leap year, 30 vs. 31). GetDate returns a full datetime so it works for any right up to the stroke of midnight.
on December 5, 2008 at 4:48 am Ken

Of course, my above comment works only when you only care about the date part. Being in the BI world, I work in dimensions of dates and ranges of time. You can change Dave’s solution to:

SELECT DATEADD(ms,-2,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

to get all the way up to midnight (”s, -1″ leaves the second before midnight out, and using “ms, -1″ rounds to the next day since datetime is accurate to roughly 3 milliseconds.

But in those cases I highly recommend using less than the first of the month not between x and this EOM value. Any time that SQL can represent up until midnight will be caught then.

Select *
from Orders
where OrderDate >= @FirstOfLastMonth and OrderDate < @FirstOfThisMonth — not <=

From a mathematical and linear time aspect, I believe that would be more correct.

This demonstrates what I think is one of SQL Servers biggest deficiencies; the lack of a simple date data type with no time component.
on December 26, 2008 at 3:22 pm Suman Prasad

Hi,
I want to get the number of days in last 21 months from now.