get the first Sunday and last Saturday

Question

Post reply

get the first Sunday and last Saturday

Viewing 8 posts - 16 through 22 (of 22 total)

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

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 1

Always worth considering a proper calendar table too..

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Clear Sky SQL
My Blog[/url]

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 2

Another minor point. I deeply disagree with the use of:

FROM master..spt_values

WHERE TYPE = 'P'

AND number BETWEEN 1 AND 12

I think it's much better to just create your own in-line table of #s from 1 to 12.

SQL Server could remove the table/rows in a future release. Or, much worse, remove a single row from that range of values. Then you would get incorrect results.

Scott Pletcher, SQL Server MVP 2008-2010

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

scott.pletcher (10/28/2010)
...SQL Server MVP 2008-2010

Pulling ranks, huh? 😀

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

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 4

Pulling ranks, huh?

Lol, nah.

I love your technique, it's great. I like that general method -- every 7 days is always the same weekday -- rather than any method that relies on @@DATEFIRST.

Scott Pletcher, SQL Server MVP 2008-2010

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

If anyone is wondering where 22801 came from:

Make Date function (like in VB)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339

Although I would have assumed that anyone would realize without explanation that 22801/12 = 1900 😉

WayneS SSC Guru Points: 95409 More actions · Answer 6

Michael Valentine Jones (10/28/2010)
If anyone is wondering where 22801 came from:
Make Date function (like in VB)
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339
Although I would have assumed that anyone would realize without explanation that 22801/12 = 1900 😉

Actually, it's 1900.083333333333. It's 22800/12=1900. When I saw the fraction, I really started puzzling over why this number was being used, and never looked to see what taking away one would do.

So, slowly, I'm starting to comprehend what is going on here...

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

In SQL Server integer math 22801/12 = 1900 = 22800/12 😀

Lowell SSC Guru Points: 323504 More actions · Answer 8

{edit added this comnetn to wrong post!}

here's what i came up with to get the first date of the month, to the "latest" sunday 5pm

there might be a shorter datemath to do the same work, it's just the rabbithole i fell down

select

--beginning of current month

DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) As StartOfMonth,

--the end of week(Sunday) plust a time component.

DATEADD(dd, DATEDIFF(dd,0,DATEADD(dd, DATEPART(DW,GETDATE())*-1 + 1, GETDATE())), 0)+ convert(time,'17:00:00') As TheSundayDate --last sunday

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!