How do I identify a particular date as a Sunday? 2

Madawc · Jul 13, 2012

I mostly write Crystal reports, sometimes using SQL to collect the data. But now I need to fix an existing report that does most of its logic in SQL and just uses Crystal to display the result.

A problem occurs when the first of a month is also a Sunday. I know how to test for this in Crystal: DatePart with "w" would be 1 for that date.

Looking in SQL, I find that DatePart does not include this option, though it can identify weekdays. I also see logic to select a Sunday or find the next Sunday. But not a simple way to test if an existing date is Sunday or not.

Can anyone help, please?

[yinyang]

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP [yinyang]

SQLScholar · Jul 13, 2012

If you have a look at the MSDN for datepart

http://msdn.microsoft.com/en-us/library/ms174420(v=sql.105).aspx

you will see that what you want is dw - which is weekday.

Please also read the bit near the middle called "week and weekday datepart Arguments" as it depends on the value of SET DATEFIRST to depend if (e.g.) sunday or monday is the first day of the week.

Hope this helps

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

SQLScholar · Jul 13, 2012

Just to clarify - weekdays doesnt bring back all weekdays. It actually brings back the day of the week in numerical form (1-7).

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

Madawc · Jul 13, 2012

I did say that I could see how to get weekdays, or non-weekdays. The problem is to tell Saturday from Sunday.

[yinyang]

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP [yinyang]

SQLScholar · Jul 13, 2012

It actually brings back the day of the week in numerical form (1-7).

So 1 = Sunday
2=Monday
3=Tuesday......

If you read the SQL 2000 one it actually is slightly clearer (same for 2008 though).

http://msdn.microsoft.com/en-us/library/aa258265(v=sql.80).aspx

The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week.

So what i was trying to say (in my round about way) is that weekday DOESNT bring back if its a weekday. It brings back the numerical reference for the day of the week. So 1 is sunday, 2 is monday.

Please also look at the SET DATEFIRST arguement because it does effect the results.

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

Madawc · Jul 13, 2012

Fine, thanks.

[yinyang]

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP [yinyang]

gmmastros · Jul 13, 2012

I prefer another method for determining if a date is on a Sunday.

The method that SQLScholar mentioned works well, but it depends on the datefirst setting. The default datefirst setting is based on the default language for your login. It looks like most of the world has a default datefirst of 7 (Germany, France, Spain, Italy, Croatia, Greece, Russia, England, etc...). The rest of the world has a default date setting of 1 (US, Japan, Portugal, Brazil, Korea, etc...).

You can see a full list by running this query:

[tt]Select * From sys.syslanguages[/tt]

There is another way to determine if a date falls on a Sunday, one that doesn't depend on the datefirst setting.

January 7, 1900 happens to be a Sunday.

Calculate the number of days between the TestDate and Jan 7, 1900.
Determine the remainder of integer division when dividing by 7. If the remainder is 0, your test date is a Sunday.

Take a look at this code:

Code:

Declare @Temp Table(TestDate DateTime)

Insert Into @Temp Values('20120708')
Insert Into @Temp Values('20120709')
Insert Into @Temp Values('20120710')
Insert Into @Temp Values('20120711')
Insert Into @Temp Values('20120712')
Insert Into @Temp Values('20120713')
Insert Into @Temp Values('20120714')

Select TestDate, 
       DatePart(Weekday,TestDate), 
       Case When DatePart(Weekday,TestDate) = 1 
            Then 1 
            Else 0 
            End As IsSunday,
       Case When DateDiff(day, '19000107', TestDate) % 7 = 0
            Then 1 
            Else 0 
            End As AnotherIsSunday
From   @Temp

Set DateFirst 1

Select TestDate, 
       DatePart(Weekday,TestDate), 
       Case When DatePart(Weekday,TestDate) = 1 
            Then 1 
            Else 0 
            End As IsSunday,
       Case When DateDiff(day, '19000107', TestDate) % 7 = 0
            Then 1 
            Else 0 
            End As AnotherIsSunday
From   @Temp

Set DateFirst 7

Select TestDate, 
       DatePart(Weekday,TestDate), 
       Case When DatePart(Weekday,TestDate) = 1 
            Then 1 
            Else 0 
            End As IsSunday,
       Case When DateDiff(day, '19000107', TestDate) % 7 = 0
            Then 1 
            Else 0 
            End As AnotherIsSunday
From   @Temp

When you copy/paste this code to a query window and run it, you'll see that that the "IsSunday" values change based on the DateFirst setting, but the "AnotherIsSunday" does not depend on DateFirst.

I haven't tested, but I suspect the DatePart method may be slightly faster, but I doubt you'll notice a difference in performance. In my opinion, the "diff and mod" method performance nearly as well, but does not suffer from differences in language or datefirst settings, making it the better choice.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

SQLScholar · Jul 13, 2012

George,

Good option... one question, if the datepart relies on the Datefirst setting - isnt it just easier to set the datefirst setting at the start? Then it doesnt matter about the default language?

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

Qik3Coder · Jul 13, 2012

@SQLScholar,
It's like turning off result counts. You can do it, but you make darn sure you turn in back on when you're done.

@GMM,
You have a known sunday hardcoded in your script, so why not just use this...

Code:

--Used GMM's Test Data
Select TestDate, 
       DatePart(Weekday,TestDate), 
       Case when DatePart(weekday, TestDate) = DatePart(Weekday, '19000107') THEN 1 ELSE 0 END NewSunday,
From   @Temp

You've got questions and source code. We want both!
There's a whole lot of Irish in that one.

SQLScholar · Jul 13, 2012

@Qik3Coder

Good point. Star just for that from me.

D

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

gmmastros · Jul 13, 2012

@Qik3Coder,

That would work also. 6 of one, 1/2 dozen of the other.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

djj55 · Jul 13, 2012

George, I am a bad boy. I ran your code on the production server my mistake. Luckily, 7 must be my lucky number as "Set DateFirst 7" is what we have.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

gmmastros · Jul 14, 2012

Set datefirst only applies to the current connection, so if you open one window in SSMS, and set the datefirst, and then open another window, the second window would not be affected.

If I post code that could mess up a server, I give a strong warning along with the code.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

simian336 · Jul 16, 2012

I like to use a calendar table.

http://sqlserver2000.databases.aspf...nsider-using-an-auxiliary-calendar-table.html

Simi

djj55 · Jul 16, 2012

George, Thanks for the information. Although I should not have been running on the production server. [sad]

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

How do I identify a particular date as a Sunday? 2

Madawc

Programmer

SQLScholar

Programmer

SQLScholar

Programmer

Madawc

Programmer

SQLScholar

Programmer

Madawc

Programmer

gmmastros

Programmer

SQLScholar

Programmer

Qik3Coder

Programmer

SQLScholar

Programmer

gmmastros

Programmer

djj55

Programmer

gmmastros

Programmer

simian336

Programmer

djj55

Programmer

Similar threads

Part and Inventory Search

Sponsor