Datetime tricks

Question

Post reply

Datetime tricks

Gail Shaw

SSC Guru

Points: 1004504
More actions
October 28, 2010 at 2:51 am

#230476

Ways of identifying the first sunday of the month, the second monday of the year, the last friday of the quarter, etc.
Or 'games with dateadd and datediff'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

I can take this on.

Just recently posted some here http://www.sqlservercentral.com/Forums/Topic1010974-338-1.aspx

and blogged a lot of them here

http://weblogs.sqlteam.com/peterl/archive/2009/11/03/Get-the-Nth-weekday-of-any-arbitrary-period.aspx

http://weblogs.sqlteam.com/peterl/archive/2009/06/18/How-to-get-the-Weekday-and-Nth-from-a-date.aspx

http://weblogs.sqlteam.com/peterl/archive/2009/06/18/Extended-Get-Nth-Weekday-of-period.aspx

Let me know what you think.

N 56°04'39.16"
E 12°55'05.25"

Richard Warr SSCertifiable Points: 6967 More actions · Answer 2

This one's handy for calculating public holidays. I've always had fun with Easter!

http://www.tek-tips.com/faqs.cfm?fid=5075

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

Seems like a lot of math calculations there...

See this http://weblogs.sqlteam.com/peterl/archive/2010/09/08/fast-easter-day-function.aspx

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

SwePeso (11/1/2010)
Seems like a lot of math calculations there...
See this http://weblogs.sqlteam.com/peterl/archive/2010/09/08/fast-easter-day-function.aspx

I love it when people can simplify that much. Well done.

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

Thank you.

I am ready to write an article about "DATETIME tricks". What now? Steve has to accept?

N 56°04'39.16"
E 12°55'05.25"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

I'd say just write and submit. It's not as if there are multiple people offering to write it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TomThomson SSC Guru Points: 104773 More actions · Answer 7

Richard Warr (11/1/2010)

This one's handy for calculating public holidays. I've always had fun with Easter!

http://www.tek-tips.com/faqs.cfm?fid=5075%5B/quote%5D

Blimey! That dbo.easter function could form the basis of an article on "How to write a Fortran II subroutine in SQL". I much prefer the one SwePeso posted a link for.

I wonder why the two have different ranges of validity - what changes would the SQLteam version require to extend validity back to 1754 instead of working only from 1900 onwards, and what changes would the tek tips function need to extend validity past 3400 to 9999?

Tom

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 8

Tom, that would be three simple steps

1) Replace 0 with '17530101'

2) Replace 6 with '17530107'

3) Replace 1900 with 1753

Done!

N 56°04'39.16"
E 12°55'05.25"

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 9

Steve Jones - SSC Editor

SSC Guru

Points: 736478

November 2, 2010 at 9:17 am

#1245481

Peso,

Take it. I'll close this thread for now.