June 12, 2008 at 2:46 pm
catriona_brown (6/12/2008)
I figured it out. An no still not an exam question.
Heh... ok, your turn... let's see it... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2008 at 3:30 pm
select
a.Date,
LastSundayOfMonth =
dateadd(dd,(datediff(dd,-53684,dateadd(mm,datediff(mm,-1,a.Date),-1))/7)*7,-53684)
from
(
--Test Data
select Date = convert(datetime,'20080101')union all
select Date = convert(datetime,'20080201')union all
select Date = convert(datetime,'20080301')union all
select Date = convert(datetime,'20080401')union all
select Date = convert(datetime,'20080501')union all
select Date = convert(datetime,'20080601')union all
select Date = convert(datetime,'20080701')union all
select Date = convert(datetime,'20080801')union all
select Date = convert(datetime,'20080901')union all
select Date = convert(datetime,'20081001')union all
select Date = convert(datetime,'20081101')union all
select Date = convert(datetime,'20081201')
) a
order by
a.Date
Results:
Date LastSundayOfMonth
------------------------------------------------------ -----------------------
2008-01-01 00:00:00.000 2008-01-27 00:00:00.000
2008-02-01 00:00:00.000 2008-02-24 00:00:00.000
2008-03-01 00:00:00.000 2008-03-30 00:00:00.000
2008-04-01 00:00:00.000 2008-04-27 00:00:00.000
2008-05-01 00:00:00.000 2008-05-25 00:00:00.000
2008-06-01 00:00:00.000 2008-06-29 00:00:00.000
2008-07-01 00:00:00.000 2008-07-27 00:00:00.000
2008-08-01 00:00:00.000 2008-08-31 00:00:00.000
2008-09-01 00:00:00.000 2008-09-28 00:00:00.000
2008-10-01 00:00:00.000 2008-10-26 00:00:00.000
2008-11-01 00:00:00.000 2008-11-30 00:00:00.000
2008-12-01 00:00:00.000 2008-12-28 00:00:00.000
(12 row(s) affected)
June 12, 2008 at 3:33 pm
Jeff Moden (6/12/2008)
Jeff Moden (6/12/2008)
I think your @firstSunday should be a 7, no? Right now, you code lists Saturdays... of course that also means your code will (should) work for any day of the week...:DWait a minute... I gotta get the shoelaces out of my mouth, first... I was looking at 2008 and you're generating for 2009. My mistake.
Well - you made me blink....:P I scurried back to test again to make sure I didn't start drinking too early this morning.....:w00t:
You're correct though - it would be easy to use this for "the last x day of the month" using any given known day of the week.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 12, 2008 at 3:55 pm
Blinking is good... keeps the bloodshot eyes in shape 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2008 at 4:58 pm
Jeff Moden (6/12/2008)
Blinking is good... keeps the bloodshot eyes in shape 😛
You spying on me again? with the various fun lately I am running rather ragged. I'm waiting for Barry to chime in on our unnaturally slow blink rate and how that's scaring the children in the neighborhood.......:hehe:
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 12, 2008 at 5:44 pm
catriona_brown (6/11/2008)
How can I retrieve the date of the last Sunday of every month?
All these wonder formula's for calculating the last sunday of a month and I'm not sure we ever answered the original question above...
If by "every month", you mean whatever the current date is, then just about any of the formulas above (or in this particular thread) will do just fine.
But, if you need to know the last Sunday of every month for a whole range of dates without duplication, then try this out...
DECLARE @DateStart DATETIME,
@DateEnd DATETIME
SELECT @DateStart = '20080131',
@DateEnd = '20101201'
SELECT DATEADD(wk,DATEDIFF(wk,6,DATEADD(mm,DATEDIFF(mm,-1,DATEADD(mm,t.N-1,@DateStart)),-1)),6)
FROM dbo.Tally t
WHERE t.N <= DATEDIFF(mm,@DateStart,@DateEnd)+1
Of course, you could always try a "Calendar Table".
If you don't already have a Tally table, now's a good time to add one to your tool chest... see the following URL for how they work and how to build one...
http://www.sqlservercentral.com/articles/TSQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2008 at 1:08 pm
In doing some tests on this, I found something I consider quite interesting.
Datepart(Day, date)
Datepart(Month, date)
Datepart(Year, date)
Datepart(Hour, date)
Datepart(Minute, date)
are all considered deterministic by SQL Server, but
Datepart(Weekday, date)
is not.
Neither are:
dateadd(day, '12/30/1899', date)%7
cast(date as float)%7
cast(date as int)
None of these can be indexed as calculated columns in a table or indexed view. Day, month, year, hour, minute, second, millisecond in datepart can all be indexed as calculated columns. Weekday can't.
All of the above formulae are very much deterministic (with the possible exception of the ones that use the Float data type), but SQL Server gave me error messages when I tried to index them, saying that they aren't.
As for the last Sunday in any given month, or for all given months, I have a Dates table and it's just:
select max(Date)
from dbo.Dates
where DateWeekday = 1
group by DateYear, DateMonth
order by DateYear, DateMonth
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 17, 2008 at 8:46 pm
catriona_brown (6/12/2008)
I figured it out. An no still not an exam question.
C'mon... don't be shy... everyone else posted their solution... it's your turn... 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2008 at 1:38 pm
Here's my version, in the form of a couple of functions. The first, LastSunday, returns the date of the last Sunday of the month passed in.
create function dbo.LastSunday(
@Target datetime
)
returns smalldatetime
as begin
if @Target is null
return NULL;
declare @LastDay smalldateTime;
-- Adding 30 days to the first day of any month is
-- shorthand for "go to last day of the month."
set @LastDay = DateAdd( Month, DateDiff( Month, 0, @Target ), 30);
return DateAdd( dd, -(DatePart( dw, @LastDay ) - 1), @LastDay );
end
It only works properly, however, when DateFirst = 7. That being the default, that just means it works most of the time.
In thinking about making it DateFirst neutral, I decided just to make it more general. So the second function, LastWeekDay, requires a second parameter, the value 1-7 which specifies the day of the week according to whatever DateFirst is set to.
create function dbo.LastWeekDay(
@Target datetime,
@Day tinyint
)
returns smalldatetime
as begin
if @Target is null or @Day not between 1 and 7
return NULL;
declare @LastDay smalldateTime;
-- Adding 30 days to the first day of any month is
-- shorthand for "go to last day of the month."
set @LastDay = DateAdd( Month, DateDiff( Month, 0, @Target ), 30);
return DateAdd( dd, -(DatePart( dw, @LastDay ) - @Day + 7) % 7, @LastDay );
end
I went to a db which had a table with a date field. A "SELECT *" returned almost 45,000 rows in about 2 seconds (according to the status bar indicator). A "SELECT *, dbo.LastWeekDay( datefield, 2 ) as LastMonday" also took 2 seconds. In my environment, that's plenty good performance.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
June 18, 2008 at 8:36 pm
GSquared (6/17/2008)
...are all considered deterministic by SQL Server, butDatepart(Weekday, date)
is not.
Neither are:
dateadd(day, '12/30/1899', date)%7
cast(date as float)%7
cast(date as int)
Aren't the first two affected by configuration settings on the server or instance?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 19, 2008 at 6:19 am
rbarryyoung (6/18/2008)
GSquared (6/17/2008)
...are all considered deterministic by SQL Server, butDatepart(Weekday, date)
is not.
Neither are:
dateadd(day, '12/30/1899', date)%7
cast(date as float)%7
cast(date as int)
Aren't the first two affected by configuration settings on the server or instance?
Yes, I think that is the reason. I figured the same yesterday when I was trying to find solution to another post, and realized, that computed column can't be made persisted if its definition contains ISDATE function.
From datetime/character conversion functions, if you need the expression to be deterministic, you can only use CONVERT, and must specify style... what more, even some of the styles are not deterministic (see BOL).
June 19, 2008 at 10:56 am
rbarryyoung (6/18/2008)
GSquared (6/17/2008)
...are all considered deterministic by SQL Server, butDatepart(Weekday, date)
is not.
Neither are:
dateadd(day, '12/30/1899', date)%7
cast(date as float)%7
cast(date as int)
Aren't the first two affected by configuration settings on the server or instance?
Yeah, I'm sure that's the reason. I just found it interesting enough to comment on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 27, 2008 at 8:51 pm
Hi
Use the below query,
-- Last Sunday (Inclusive)
SELECT DATEADD(day, DATEDIFF(day,
0 /* Any Base Date */,
GETDATE()) - (DATEDIFF(day,
6 /* Base Sunday Date */,
GETDATE()) % 7), 0 /* Any Base
Date */);
Thanks -- Vj
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply