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://blog.SQLAuthority.com)
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
>> 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
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′))
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
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′
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
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.
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
declare @month int, @year int
select @month=3,@year=2008
select dateadd(year,@year-1900,dateadd(month,@month,0)-1)
Madhivanan,
This query is returning 2008-02-28 if i set @month as 2, where in it should actually return 2008-02-29 as this is leap year.
Madhivanan,
This query is 2008-02-28 if i set @month as 2 where in it should actually show 2008-02-29 as its a leap year.
Try this code
declare @month int, @year int
select @month=2,@year=2008
select dateadd(year,@year-1900,dateadd(month,@month,0))-1
Sir,
I want how to search the information based on giving a parameter
Where col=@parameter_value
declare @var varchar(10)
set @var=’fn_sqlvarbasetostr’
select * from sysobjects where name like ‘%’+@var+’%’
Hi Dave,
I want to get datetime in form of yyyymmdd from stored proc. Can u guide me for this. Thanks in advance
If you use front end application, do the formation there
Otherwise
select convert(char(8),getdate(),112)
Now everyone, please, read this. Actually, read it twice! :)
Never, ever do formatting anywhere but in UI layer. If you follow MVC model, View is the place for that. And why is this?
I worked for two years in a project where things were formatted in Model, View and in Controller layers. I jumped in in the support phase so all the code was already written. I don’t know if it was ever tested though.
But. Sometimes culture settings were changed in the ASP.NET server, I really don’t know why or who, so simple parsing from “1,234,567.89″ to Decimal became awfully difficult to do because in Finland we use dots as thousand separator, not commas.
This lead into a situation where user really wanted to enter the value 1234567.89 but ended up with 1.23456789 and vice versa when I displayed values I got from a Web Service.
So I had three layers where formatting and casting could, and did, go wrong and the program wasn’t the tiniest one. So I spent months in fixing just currency string currency conversions!
Also the program was written in VB.NET and did not have OPTION STRICT set ON. This lead to some interesting situations with implicit castings from string to int/double and back to string.
How all this could have been avoided? By keeping the currency values in Decimal data type everywhere in the program and formatting would have been done only when displaying them to the user / reading user input.
use ‘ like’ operator
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?
use this and you will get select CONVERT(DATE, GETDATE(), 102)
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.
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
How to find the last date of given year’s month.
See if this helps
declare @month int, @year int
select @month=3,@year=2008
select dateadd(year,@year-1900,dateadd(month,@month,0)-1)
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……..
how to find last monday of month? any ready functions please……….
Thanks……….
Try this
SELECT DATEADD(week, DATEDIFF(week,0,dateadd(month,datediff(month,0,GETDATE())+1,0)), 0)
SELECT DATEADD(week, DATEDIFF(week,0,dateadd(month,datediff(month,0,’2/28/2010′)+1,0)), 0) This works fine as long as the last day of the month is not a Sunday. I too am looking to find a date fuction to find the Last Monday of the month.
Our developer figured this out. Will work if Sunday is the last month and Leap Year:
DECLARE @monthOfInterest smallDateTime
DECLARE @dayOfInterest tinyint
SET @monthOfInterest = ’2/29/2012′
SET @dayOfInterest = 5
SELECT
DATEADD(day, -(7 + DATEPART(weekday, ( DATEADD(day, -DATEPART(day, DATEADD(month, 1, @monthOfInterest)), DATEADD(month, 1, @monthOfInterest)) )) – @dayOfInterest)%7,
DATEADD(day, -DATEPART(day, DATEADD(month, 1, @monthOfInterest)), DATEADD(month, 1, @monthOfInterest)) )
This is great, thanks! Only thing when copying note that:
…@monthOfInterest)) )) – @dayOfInterest)%7,….
Should be a Minus sign in there but if you direct-copy it, it is something slightly longer than a minus sign, so just replace.
alter function fn_lastMonday
(
@date smalldatetime
)
returns smalldatetime
As
Begin
declare @lastdate datetime
set @lastdate =DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@date)+1,0))
return DATEADD(d,-(DATEPART(weekday,@lastdate)-1),@lastdate)
End
For Testing
declare @date smalldatetime
set @date=’2010-12-05′
select dbo.fn_lastMonday(@date),DATENAME(dw,dbo.fn_lastMonday(@date))
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.
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.
Hi,
I want to get the number of days in last 21 months from now.
select datediff(day,dateadd(month,-21,getdate()),getdate())
Dave, thanks its helped me alot.
[...] SQL SERVER – Find Last Day of Any Month – Current Previous Next [...]
Thanks Dave, your programming is very helpful.
Very helpful. Thanks for sharing!
Hi Dave,
Can someone tell me how to get previous year previous month Date from todays date? I need to know, how we will get 02/29/2008.
Thanks in advance.
-Peter
Previous year, previous month last date
select dateadd(month,datediff(month,0,dateadd(year,-1,getdate())),-1)
No need to use DateDiff. Inside Dateadd we can use another DateAdd .
Eg. SELECT DATEADD(DD,-1,DATEADD(M, 2,GETDATE()))
how to find the day of month ……..
only enter month
ex:month=04
then
find the last date of this month…..using vb.net
If you want to pass only month, what about year?
Which year do you want to combine?
You’re the man!
Whenever I have a question google sends me to you…
And you always have an answer.
Kudos to you!
I need to find the last day of the previous month based on a date entered by a user.
Also, the date the user will be entering will always be the last day of some month.
Thanks,
@Donna
You can subtract the amount of days in the passed date. That will give the last day of the prior month.
DECLARE @A DATETIME
SET @A = ’04/30/2009′
select DATEADD(d, -DATEPART(d, @A), @A)
in short
DECLARE @A DATETIME
SET @A = ’04/30/2009′
select DATEADD(day, -DAY(@A), @A)
Please help. In my stored procede, I want to assign the return value of ‘SELECT DATEADD(dd, -Day(GetDate()), GetDate())’ to a variable, e.g.
SET @PreMonthEnd = SELECT DATEADD(dd, -Day(GetDate()), GetDate())
What is the correct statement I should write? Thanks a lot.
SET @PreMonthEnd = DATEADD(dd, -Day(GetDate()), GetDate())
All above statements seems complicated to find last date of previous month… how abt this
select dateadd(d,-day(getdate()),getdate())
Thanks ,sunny
It’s very useful for me …………thanks a lot
thanks a lot man
u r awesome
thank u
Most beneficial,
Thanks
what will select dateadd(s,-1,dateadd(mm,datediff(m,0,getdate())+1,0))) return?
thanks
You need to test it yourself
It is currently giving error
hi there
I need to have a bimonthly report i.e. once with 01/01/2009 to 15/01/2009
and then 16/01/2009 to 31/01/2009
i want to know how can i get starting and ending dates like 1 and 15 of current month if the current date is > 15 and 16 to 30/31 of the previous month if current date is < 15
thanks a lot in advance.
zee
@zee
WHERE date-COLUMN >= CASE
WHEN DATEPART(dd, GETDATE()) < 16 THEN DATEADD(DD, 15, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) – 1, 0))
ELSE DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)
END
AND date-COLUMN <
CASE
WHEN DATEPART(dd, GETDATE()) < 16 THEN DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)
ELSE DATEADD(DD, 15, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) – 1, 0))
END
Pinal, your script worked perfectly. Thank you very much for sharing this!
Thank you very much for sharing this!
how to find first and last day of any month in sql?
select dateadd(month,datediff(month,0,getdate()),-1)
Excellent !
the best answer I found !
thanks
SELECT DATEADD(mm, DATEDIFF(m, 0, GETDATE()),1) -1
will return last day of current month (looks shorter)
Correction
SELECT DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0) -1
will return last day of current month (looks shorter)
How do I get the Date that is 3 months prior than today? So, three months ago from today.
PN
Actually, I discovered it: dateadd(month, -3, GetDate())
This will give date and time.
I am working on formatting to get only the date.
Thank you.
If you use front end application, do formation there
Really useful stuff – thanks!
Thanks your Help a lot.
Simple One!!!!
For Last day of last Month
SELECT getDate() – datePart( d,getDate() )
This is much shorter code
SELECT getDate() – day(getDate())
Thanks Pinal
How do I pull data from starting day of current year through last day of the previous month(pl. include convert function also).
thanks a lot.
select colums from table
where
date_col>=dateadd(year,datediff(year,0,getdate()),0) and
date_col<dateadd(month,datediff(month,0,getdate()),0)
@mos: [Edited one statement] Check your code before posting it. And if it works your date don’t show the correct time.
@jack
checked code:
select getdate() – datePart(d,getDate())
works fine.
Convert date into 103 or 102 format using convert()
With this you can remove time part by
select dateadd(day,datediff(day,0,getdate()-datePart(d,getDate())),0)
try this one
select CONVERT(varchar(25), getDate() – datePart(d, getDate()) ,103 )
The date value becomes varchar
The effecient method is
select dateadd(month,datediff(month,0,getdate()),-1)
Another method for removing time part from datetime
select CAST(floor(cast(getdate() as float))as datetime)
But this may be slower for large number of data
To Madhivanan
Will you please explain how this may be slower for large number of data. Because I am a beginner in Sql and not have much knowledge. Please Explain it…
Thanks In Advance…….
It is becuase you are converting date to float and again convert to date. It may take extra time.
Whereas the method I posted wont do any convertion
This will give the Number of days in current month.
try it
select datediff(d,getdate(),dateadd(m,1,getdate()))
Another method
select day(dateadd(month,1,getdate()-day(getdate())+1)-1)
Could u give me idea about working days in a Month,According to UK??
tell me the rule of working day in UK. I have a function which calculate weekoffs as any compny.
In my compny Sat and Sun are weekoffs. what about yours
Sat n Sun are off Plus in UK there are 8 Bank Holidays.
You can google it and find out about Bank Holidays in UK.
Some Holidays are not fixed on date.Examle
In May there are Bank Holidays,which is first Monday and Last Monday Of May.
Cheers
/*
This function will return total weekends and bank holidays
*/
CREATE FUNCTION test2
(@startDate smalldatetime, @endDate smalldatetime)
RETURNS @weekOffs TABLE (dates smalldatetime,Type char(2))
AS
BEGIN
Declare @day tinyint
Begin
SELECT @day = 7- datepart(weekday, @startDate)
SELECT @startDate = DATEADD(Day, @day, @startDate)
while @startDate<@endDate
BEGIN
INSERT INTO @weekOffs(dates,Type)
SELECT @startDate,case isBankHoliday(@startDate) when 0 then 'WO' else 'BH' end
INSERT INTO @weekOffs(dates,Type)
SELECT DATEADD(Day, 1, @startDate),case isBankHoliday(DATEADD(Day, 1, @startDate)) when 0 then 'WO' else 'BH' end
SELECT @startDate = DATEADD(Day, 7, @startDate)
END
RETURN
END
CREATE function isBankHoliday(@date smalldatetime)
returns bit
as
Begin
declare @result bit
if Exists(select * from bankholidayTable where CONVERT(varchar,bankHolidayDate,105)=CONVERT(varchar,@date,105))
set @result=1
else
set @result=0
return @result
End
Cheers Sumit
I ‘ll try that function later on.
thanks for ur help
i want to calculate the number of day in a previous months.
because i want to calculate the no.of workig day in a previous month
suggest me
This will give you the number of days in any month
Try It
declare @MonthYear smalldatetime
set @MonthYear=’2010-04-05′
select datediff(d,@MonthYear,dateadd(m,1,@MonthYear))
select day(dateadd(month,datediff(month,0,getdate()),-1))
after You have get the total no of days subtract the week-offs and holidays from them rest are the working days…
To get the date and time as midnight without having to do a cast convert etc,
i.e. ’2010-05-31 00:00:00.000′
Just change the seconds to -86400 rather than -1 which is the number of seconds in a day.
E.g.
DECLARE @Date DATETIME
SET @Date = DATEADD(s,-86400,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
Most reliable method is
select dateadd(month,datediff(month,0,getdate()),-1)
Hi all,
how to get start date and end date from the following duration as ‘Jan 10 – Jun 10′?
Can you tell any suggestion as soon as,
thanks to all
Thanks, The above said query very useful for us
Dear sir,
how to calculate days on selected dropdownlist in dropdownlist(jan,feb,march,….)
i.e. (jan=31,Feb=28/29,….)
This is for the current month and year
select day(dateadd(month,datediff(month,0,getdate())+1,-1))
Nice article. Very professional.
Just wanted to drop a note…
These will not work with smalldatetime. Only datetime. If it was mentioned earlier… missed it.
Yes it is becuase of minute rounding
Thank you.. very good example…
Upto – 10 weeks how to calculate the date
Can you post some sample data with expected result?
This may sound like a strange question but how do you find the first day of month say 3 months before Last Day of Previous Month(as defined by “SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) “)
So my script looks something like this
and startdte >= “”Here is where I needthe first day of the month three months prior to the last day of the previous month”"
and startdte <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
SELECT dateadd(month,datediff(month,0,getdate())-4,0)
I need to amend the query to take weekends into consideration – more precisely, I would like to get the last day in a month that is not a weekend. Is it possible to write such an sql query?
Cheers.
Try this logic
select last_date+case when datepart(weekday,last_date) =1 then -2 when datepart(weekday,last_date) =7 then -1 else 0 end from
(
select dateadd(month,datediff(month,0,’20090120′)+1,-1) as last_date
) as t
Thans a ton! As you already know, it works (like a charm). I would not have been able to solve this on my own =) Well done!
Great solution! Thanks!
Hello Again,
Is it possible to test if the selected date is a monday??
Cheers.
use this
select DATENAME(dw,getdate())
Please help me with this, i have a table with the follwoing fields:
Date
Duration (in Months, eg 6 months, 12 month or 18 months)
StartDate
EndDate
from StartDate, I want to find out last date of the month as EndDate, which is determined by Duration(in Months).
Regards.
This is the best way to find last day of the month in sql server
SELECT DISTINCT DATEADD(MONTH, DATEDIFF(MONTH, 0,GETDATE())+1,-1)
Also note that you dont need to use distinct in this case
it is very nice artical
i have the following code to get the records from today to two months in the past (from 1st day of that month), how can i add condtion to get the months from previous year e.g. december 2010 when i run the query in 2011
dt_updated > (select DATEADD(MONTH,-2,DATEADD(DAY,1-DAY(CURRENT_TIMESTAMP),CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))))
select dateadd(year,datediff(year,0,getdate())+1,0)-1
thans Madhivanan,
I added the suggested line of code and run the query now I’m not getting any records.
dt_updated > (select DATEADD(MONTH,-2,DATEADD(DAY,1-DAY(CURRENT_TIMESTAMP),CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))))
and
dt_updated > (select dateadd(year,datediff(year,0,getdate())+1,0)-1)
perhaps I need to combine the two into one. any hint?
I am using the first of previous month and last of previous month….
WHERE prof.profdate between DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)) and DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
I keep getting some results with dates for 12/01/2010…I am only looking for the dates between 11/01/2010 and 11/30/2010…
The prof.profdate column is smalldatetime…Does anyone see what I am doing wrong?
where
prof.profdate>=dateadd(month,datediff(month,0,getdate())-1,0) and
prof.profdate<dateadd(month,datediff(month,0,getdate()),0)
I didnt understand anything
explain me the logic fully please
Tell us what you didn’t understand
How will find the first day of current previous month?
Your posts are always very helpful and teach me so much. Thank you!
thank u very much.u save me.
Just want to say “thank you”, using your site alot
ok, so I am stuck and you all seem to know what you are taling about! (and you are going to think this query is really simple!)
I have this
(dateadd(d,-1,getdate()))
and I have added it into the SQL function in Excel. When it is refreshed it changes the data to the time that it currently is.
I want this to show data from the previous day midnight to midnight.
Can anyone explain in plain english how I do this please?
sarah
last date of current month
select DATEADD(m,1,GETDATE() -DAY(getdate()))
last date of next month
select DATEADD(m,2,GETDATE() -DAY(getdate()))
last date of previous month
select DATEADD(m,0,GETDATE() -DAY(getdate()))
I am sorry if someone has already suggested it
More informations
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/21/understanding-datetime-column-part-iii.aspx
I am sorry if i am wrong but this gives wrong result if you pass 01 march
declare @d smalldatetime
set @d=’2011-03-01′
–last date of current month
select DATEADD(m,1,@d -DAY(@d))
–last date of next month
select DATEADD(m,2,@d -DAY(@d))
–last date of previous month
select DATEADD(m,0,@d -DAY(@d))
the following gives right on my pc..
declare @d smalldatetime
set @d=’2011-03-01′
–last date of previous month
select DATEADD(m,0,@d -DAY(@d)+1)-1
–last date of current month
select DATEADD(m,1,@d -DAY(@d)+1)-1
–last date of next month
select DATEADD(m,2,@d -DAY(@d)+1)-1
You can get last days of all months with the below code:
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+number-1,0))from master..spt_values where type=’P’
and number>=0 and number<12
Dear all,
should the following code bring the Last day of Previous Month? thanks!
DATEADD(s, -86400, DATEADD(mm, DATEDIFF(m, 0, GETDATE()),0)))
Dear all,
How to get all Sunday’s of current month ?
Thanks,
Mahesh T.
use it select DATENAME(dw,getdate())
or
select DATENAME(weekday,getdate())
Here is the code
select date from
(
select dateadd(day,number,dateadd(month,datediff(month,-1,getdate())-1,0)) as date from master..spt_values
where type=’p’ and number between 0 and day(dateadd(month,datediff(month,-1,getdate())+1,-1))-1
) as t
where datename(weekday,date)=’sunday’
Thanks. Now I need help fixing this hole in the wall from bagging my head. Great use of functions!
i need to get latest records for a group of products,that is
latest time and updated quantity of each product
select * from table
where datecol=(select max(datecol) from table)
Thanks for your reply.
Your query would only return the record with latest date but not all records.
In my case there are different products and the product quantities are not updated on daily basis.
For eg.
I have 3 products ..
Updateddatetime Productname qtyupdated
2011-01-28 10:25:47.253 p1 10
2011-01-29 12:25:47.253 p1 20
2011-02-20 10:25:47.253 p2 10
2011-02-22 10:25:47.253 p2 90
2011-02-20 10:25:47.253 p3 50
2011-02-22 11:25:47.253 p3 60
I should get the result as
Updateddatetime Productname qtyupdated
2011-01-29 12:25:47.253 p1 20
2011-02-22 10:25:47.253 p2 90
2011-02-22 11:25:47.253 p3 60
that is latest of each record.
Please help
select max(Updateddatetime) as Updateddatetime,Productname, max(qtyupdated) as qtyupdated from table
group by Productname
How would I select all from a previous month?
No,
This would not give the desired result.
max(Updateddatetime) will always give the latest date not all latest records.
Thanks for your reply.
Your query would only return the record with latest date but not all records.
In my case there are different products and the product quantities are not updated on daily basis.
For eg.
I have 3 products ..
Updateddatetime Productname qtyupdated
2011-01-28 10:25:47.253 p1 10
2011-01-29 12:25:47.253 p1 20
2011-02-20 10:25:47.253 p2 10
2011-02-22 10:25:47.253 p2 90
2011-02-20 10:25:47.253 p3 50
2011-02-22 11:25:47.253 p3 60
I should get the result as
Updateddatetime Productname qtyupdated
2011-01-29 12:25:47.253 p1 20
2011-02-22 10:25:47.253 p2 90
2011-02-22 11:25:47.253 p3 60
that is latest of each record.
Please help
i want date range of one month where any data entered between this month should not duplicate. if data entered after the month should allow to enter.
You need to check like this
if not exists(select * from table where date_col >=@date1 and date_col<dateadd(day,1,@date2))
insert statement
Q1) Given Month, Year and Day.
List out all the “Monday” dates of current month and year
Q2) Suppose loan has been taken on 1-Jan-2001 and after exactly One Month the Due will come for Installment payment (i.e. on 1-Feb-2001)
So find out the Due date of the Installment if on any random date loan is given
IF(DATEPART(dd,GETDATE())=(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(m,0,getdate())-1,0),112),1,6)+’16′)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE(),112),1,6)+’16′)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(m,0,getdate())-1,0),112),1,6)+’16′)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE(),112),1,6)+’16′)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) <=CONVERT(VARCHAR,GETDATE(),112) GROUP BY VCR_PAY_TO
END
–UNION ALL
IF(DATEPART(dd,GETDATE())=(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(m,0,getdate())-1,0),112),1,6)+’16′)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(m,0,getdate())-1,0),112),1,6)+’16′)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE(),112),1,6)+’16′)
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) =(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),112))
AND CONVERT(VARCHAR,VCR_VOUCHER_DT,112) <=CONVERT(VARCHAR,GETDATE(),112) GROUP BY VCR_PAY_TO
END
hi all, i need to make union this two if condition Queries with groupby condition,
pls help…… Thanks in advance
Sir how can i show all months of a year ????
You need to have a calender table for this and query from that table
how i can show all months of a year
I need to find records dated last day of the month..any suggestions?
thx alot
Arthur, You are an [removed word]. Your method of calculating the last day of the month does not work. You should test your code before you post it for the world to see. I have wasted so much time attempting to implement your solution. Thanks
Steve: Arthur’s code does work, you just need to replace the single quotes with real single quotes. Unfortunately the html reply box on this page alters single quotes to this symbol ‘
Still, it’s not elegant as you have to supply both the year and month, and the datatype returned is a string so overall not very useful.
Thank’s a lot
hi ,
I m new to sql server.
I have question like i want to display records of last 30 days from currentDate with additional column say DayNo,monthNo,yearNo which contains only day,month,year…
Can anybody help me out…
Thanks .
Sir I want to calculate previous months records from my table .
like now is month january ,then i want to calculate dec 2011
count records .
Please help me
Hi Pinal,
I want a function to calculate the last day of all the months based on Year number and Month number but not based on GetDate() that gives the current Date.
Please help me out.
Thanks in Advance,
Samyuktha
Holy crap!
Can’t you just do this to get the last date of the month?
SELECT DATEADD(d, -1, DATEADD(M, DATEDIFF(M, 0, @SomeDate)+1, 0)) AS NewDate
Write a function to return total experience in ,months/years according to the given input(M/Y). Use this in a SP to get the details of a person with his name, experience.
how can i find out the last day of previous year using sql..
for example today is 03rd march 2012 , i have to get a result in the following format ’2011-12-31′ Plz help me… thanks in advance
SELECT CONVERT(VARCHAR(10),DATEADD(MILLISECOND, -3,
DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, -1,
GETDATE())) + 1, 0)),120)
select dateadd(year,datediff(year,0,getdate()),0)-1
Best one who willing to learn SQL in Deep.
This works perfectly
CONVERT(VARCHAR(8),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)),1)
but can someone explain WHY? Please :)
Hey Guys:
Would love some help on this. There is an “Accounting_Date” column in my table and I would like to create a query to reference this date returning the last day of the month. For example if the date column is 2010-01-13 00:00:00.000, I would like it to return 01/31/2010. Is there anyway to make that happen? Thanks in advance.
For those who want date only (ie without the time)use this
SELECT DATEADD(d, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)) AS LastDayOfPrevMonth
or simply
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)-1 AS LastDayOfPrevMonth
Hello. Sir.. I want to get records like..
Month Count
Jan 012 10
Feb 22
march 2
from current month to last all months, current month is May and i want to get Jan, Feb,March, Apr, and May related records… Please help me…
Find out examples available here
http://beyondrelational.com/modules/2/blogs/70/posts/10899/understanding-datetime-column-part-iii.aspx
How about to find last month (max) but the date is appear all.
For example in the database there are:
(year-month-day)
2012-04-01
2012-04-02
2012-04-03
2012-05-01
2012-05-02
2012-05-03
In this case, I just want 2012-05-01, 2012-05-02, and 2012-05-03 would appear (current month).
What is in query? Cause I tried >
SELECT CURRENTMONTH FROM DATE WHERE CURRENTMONTH = (SELECT MAX(CURRENTMONTH) FROM DATE)
Query result is only show the last month and last day > 2012-05-03.
Thanks.
While these queries do work, when I tried to use them within a CONVERT that converted the result to a varchar and used it as part of a where clause, it took over two minutes to execute against my database. I looked around and found another way of getting the last day of the month, as seen in this post http://vadivel.blogspot.com/2012/03/find-last-day-of-month.html, put that within the CONVERT and the query took less than a second to run. You may want to look at the other post if you are trying to get the last day of the month since it offers three different ways of getting the same result. Just more alternatives to consider.
Also look at the examples given at the end of this post
http://beyondrelational.com/modules/2/blogs/70/posts/10899/understanding-datetime-column-part-iii.aspx
Hi,
Wondering how I can get 22nd Date of Previous month. eg if today is 25/06/2012 then last month 22nd Date should be 22/05/2012.
Thanks
select dateadd(day,22,getdate()-day(getdate()))
how to get next 6th months date from todays date
SELECT dateadd(month,6,getdate())
Plenty of good examples. Thanks
thanks dude; i was trying to write a sql script.
Thanks a lot mate.
How do I find the last 12 months from a given date including the current month. For example:
If the given date is 15-Jan-2012
It should return; jan 2012, dec 2011, nov 2011, oct 2011, sept 2011, aug 2011, july 2011, june 2011, may 2011, apr 2011, mar 2011, feb 2011
or if the given date is 31-Dec-2011
It should return; dec 2011, nov 2011, oct 2011, sept 2011, aug 2011, july 2011, june 2011, may 2011, apr 2011, mar 2011, feb 2011, jan 2011
I use this code to return the last friday in any month:
SELECT DATEADD(day, (DATEDIFF (day, ’2010-01-01′, (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))) / 7) * 7, ’2010-01-01′) AS “LAST_FRIDAY_OF_MONTH”
There’s a way shorter method in SQL Server 2012:
SELECT [MonthEnd] = EOMONTH()
Enjoy.
i need to get the tranaction details happend between current date and same date of prevoius month.how can i .pls help
Look at the examples posted at the bottom of this post and you will have an idea
http://beyondrelational.com/modules/2/blogs/70/posts/10899/understanding-datetime-column-part-iii.aspx
Hi,
I am trying yo replicate the ‘n’th week and nth day of that week from the defined calender table. can anyone help me in writing query for the same, thanks..
Velmurugan
Awesome! Thanks a lot, this is what I was looking for.
-Abhi
Hi. Thanks for the post.
This returns 2012-10-31 23:59:59.000
How to make it to return 2012-10-31 00:00:00 000 ?
DECLARE @MyDate datetime
SET @MyDate=’15/03/1977′
DECLARE @Interval int
SET @Interval=0
SELECT DATEADD(day,-day(@MyDate),DATEADD(mm, 1+@Interval,@MyDate))
USE [TestDataBase]
GO
/****** Object: UserDefinedFunction [dbo].[LAST_DAY] Script Date: 11/08/2012 00:48:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: Luis R. Vela Morales
– Create date: 01/11/2012
– Description: De una fecha dada, retorna la
– fecha con el ultimo dia del mes,
– esto decrementando, sin afectar o
– incrementando en ‘n’ meses
– ( -n, 0, n).
– =============================================
CREATE FUNCTION [dbo].[LAST_DAY]
(
– Add the parameters for the function here
@Fecha datetime,
@Intervalo int
)
RETURNS datetime
AS
BEGIN
– Declare the return variable here
DECLARE @Result datetime
– Add the T-SQL statements to compute the return value here
SELECT @Result = DATEADD(day,-day(DATEADD(mm, 1+@Intervalo,@Fecha)),DATEADD(mm, 1+@Intervalo,@Fecha))
– Return the result of the function
RETURN @Result
END
Thnks sir providing code for getting last date of previous month in sql server.
I need a solution …
Here are the details….
Step a) I will select a month and year(eg. January 2012) from UI.
Step b) It should return four months back data (only month number).
for this purpose i am trying to use datediff(m,,) function, this is perfectly working for current year( from May to December month).
but when i am selecting January month it is returning current years September month but i need to get last year’s September month. Same as for February, March, April month.
Please help.
for Last Date of previous month
select DATEADD(D,-( DATEPART(D,GETDATE())),GETDATE())
select GETDATE()- DAY(GETDATE())
or
select EOMONTH(GETDATE(),-1) /* in SQL 2012*/
will give same result
You can find lot of such methods at the bottom section of http://beyondrelational.com/modules/2/blogs/70/posts/10899/understanding-datetime-column-part-iii.aspx
–Alternate method
DECLARE @MyDate DATETIME
SET @MyDate = ’26-Jul-2013′
SELECT DATEADD(d, -1*day(@MyDate), DATEADD(mm,-1,@MyDate))
SELECT DATEADD(d, -1*day(@MyDate), @MyDate)
SELECT DATEADD(d, -1*day(@MyDate), DATEADD(mm,1,@MyDate))
–Alter Nate Method
DECLARE @MyDate DATETIME
SET @MyDate = ’26-Jul-2013′
– Previous Month
SELECT DATEADD(d, -1*day(@MyDate), @MyDate)
– Given Month
SELECT DATEADD(d, -1*day(@MyDate), DATEADD(mm,1,@MyDate))
– Next Month
SELECT DATEADD(d, -1*day(@MyDate), DATEADD(mm,2,@MyDate))
Thank you very much for your post !
I want exact month end date so
declare @date datetime
set @date=’31-jan-2012′
while @date<='31-jul-2012'
Begin
print @date
set @date= DATEADD(M,1,@date)
End
but i'm getting result like
Jan 31 2012 12:00AM
Feb 29 2012 12:00AM
Mar 29 2012 12:00AM
Apr 29 2012 12:00AM
May 29 2012 12:00AM
Jun 29 2012 12:00AM
Jul 29 2012 12:00AM
Please help me to get exact month end date
declare @date datetime
set @date=’20120101′
select dateadd(month,datediff(month,0,dates)+1,-1) as last_day from
(
select dateadd(month,number,@date) as dates from master..spt_values
where type=’p’ and number between 0 and 6
) as t
Just Create this Stored Procedure in a Data Base
And call it
============================
USE [TestDataBase]
GO
/****** Object: UserDefinedFunction [dbo].[LAST_DAY] Script Date: 11/08/2012 00:48:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: Luis R. Vela Morales
– Create date: 01/11/2012
– Description: De una fecha dada, retorna la
– fecha con el ultimo dia del mes,
– esto decrementando, sin afectar o
– incrementando en ‘n’ meses
– ( -n, 0, n).
– =============================================
CREATE FUNCTION [dbo].[LAST_DAY]
(
– Add the parameters for the function here
@Fecha datetime,
@Intervalo int
)
RETURNS datetime
AS
BEGIN
– Declare the return variable here
DECLARE @Result datetime
– Add the T-SQL statements to compute the return value here
SELECT @Result = DATEADD(day,-day(DATEADD(mm, 1+@Intervalo,@Fecha)),DATEADD(mm, 1+@Intervalo,@Fecha))
– Return the result of the function
RETURN @Result
END
Hi Dear,
Very Important article of SQL Developers
Thank you.
Last Year last month (if today is 30-Apr-2013, it should reflect 30-Apr-2012)