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
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?