Last Date Of Month script
-
The featured script to retrieve the last day of the month could be made simpler. The last day of a month, is the day prior to the first day of the next month; so no need to make loops. Simple add one month and step back one day:
declare @m int
declare @y int
set @m = 9
set @y = 2004
-- the last day of a month, is the day prior to the first day of the next month
select day(dateadd(day, -1, dateadd(month, 1, CONVERT(char(2), @m) + '/' +'01/' + CONVERT(char(4), @Y))))
-
Last day of current month
select day(dateadd(day, -1, dateadd(month, 1, getdate())))
-
Newbie,
For '2004-01-30', your SQL returns 28.
This is, obviously, not the last day of the month in question, January.
Try this:
Determine the first day of the specified month;
Add 1 month;
Subtract 1 day.
DECLARE @Date datetime
SET @Date = '2004-01-30'
SELECT DATEADD(d, -1, DATEADD(m, 1, DATEADD(d, -1 * (DAY(@Date) - 1), @Date)))
-
This works,
add 1 month, sub days of date
select convert( datetime,right('00'+convert(varchar(2), month(@date)),2) +
'/' + right('00'+convert(varchar(2), DAY(DATEADD(d, -DAY(DATEADD(m,1,@date)),DATEADD(m,1,@date)))),2) + '/' +
convert(varchar(4),year(@date)))
-
Why make it harder than it needs to be?
-- 2003-03-11 / Kenneth Wilhelmsson
-- This is a sample matrix showing how to find out dates of month boundries
-- from any given point in time.
set nocount on
declare @date datetime
set @date = '2004-01-30' --getdate() -- the point in time from which to measure
print '''Today''s date'' is: ' + convert(char(10), @date, 121)
print ' '
-- date of the 1st of the current month
select convert(char(6), @date, 112) + '01' as '1st this month'
-- date of the last day of current month
select dateadd(day, -1, dateadd(month, 1, convert(char(6), @date, 112) + '01')) as 'last this month'
-- date of the 1st of the previous month
select dateadd(month, -1, convert(char(6), @date, 112) + '01') as '1st of last month'
-- date of the last day of the previous month
select dateadd(day, -1, convert(char(6), @date, 112) + '01') as 'last of last month'
-- date of the 1st of the next month
select dateadd(month, 1, convert(char(6), @date, 112) + '01') as '1st of next month'
-- date of the last day of the next month
select dateadd(day, -1, dateadd(month, 2, convert(char(6), @date, 112) + '01')) as 'last of next month'
set nocount off
'Today's date' is: 2004-01-30
1st this month
--------------
20040101
last this month
------------------------------------------------------
2004-01-31 00:00:00.000
1st of last month
------------------------------------------------------
2003-12-01 00:00:00.000
last of last month
------------------------------------------------------
2003-12-31 00:00:00.000
1st of next month
------------------------------------------------------
2004-02-01 00:00:00.000
last of next month
------------------------------------------------------
2004-02-29 00:00:00.000
/Kenneth
-
Great date samples guys! I love the archives!
In using Kens example above, wouldn't this be better for LastDayLastMonth?
Dateadd(Day,-Datepart(day,@TruncDate)+1,@TruncDate) -.0001 as LastDayLastMonth
This is simply FirstDayThisMonth - .0001 thus giving me (for a getdate of 2005-01-04 09:37:50.770) a date of 2004-12-31 23:59:51.360
I figure if someone used LastDayLastMonth in the previous post such as:
SELECT *
WHERE somedate IS BETWEEN FirstDayPrevMonth AND LastDayLastMonth
Wouldn't any record timestamped after midnight on the last day of the previous month get filtered out?
Thanks,
David McAfee
-
SELECT *
WHERE somedate IS BETWEEN FirstDayPrevMonth AND LastDayLastMonth
Wouldn't any record timestamped after midnight on the last day of the previous month get filtered out?
Yes, they will get filterered out. That's a pitfall when querying dates that contain time fractions. To do this properly you need to use something like
SELECT *
WHERE somedate > = FirstDayPrevMonth AND < FirstDayCurrentMonth
In case you're not doing this within a view, but rather a stored procedure, you can use this pretty cool script by SQL Server MVP Steve Kass to determine the last day of a month:
declare @31st datetime
set @31st = '19341031' -- any 31st
declare @now datetime
set @now = getdate()
select dateadd(month,datediff(month,@31st,@now),@31st)
------------------------------------------------------
2005-01-31 00:00:00.000
(1 row(s) affected)
The coolest thing here is that you can virtually use any allowed 31st (note: no other day!) as a starting date. No matter if past, present, or future.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
My personal favorite is (for all you Brits out there) –
Select convert(varchar(10),dateadd(month,1+datediff(month,0,getdate()),0)-1,103) AS [EOM]
-
wow, I tried it for next month (February) and it returns the 28th, pretty cool.
declare @31st datetime
set @31st = '19341031' -- any 31st
declare @now datetime
set @now = dateadd(month,1,getdate())
select dateadd(month,datediff(month,@31st,@now),@31st)
----------------------------------------
2005-02-28 00:00:00.000
-
Guess I'll add my two (or is it 3?) cents...
------ Finds the LAST day of CURRENT month with no time element SELECT DATEADD(mm,DATEDIFF(mm,0,GetDate())+1,0)-1 AS LastDayCurrentMonth
------ Finds the LAST day of CURRENT month (Time=23:59:59.997) (resolution is 3 ms) SELECT DATEADD(ms,-3,DATEADD(mm,DATEDIFF(mm,0,GetDate())+1,0)) AS LastDayCurrentMonth
------ Finds the FIRST day of CURRENT month (Time=00:00:00.000) SELECT DATEADD(mm,DATEDIFF(mm,0,@Date),0) AS FirstDayCurrentMonth
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply