INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...This is easily the most helpful website I've ever used, and this is the best forum with the quickest response time bar none...."
Geography
Where in the world do Tek-Tips members come from?
|
How do I identify a particular date as a Sunday? (2)
|
|
|
Madawc (Programmer) |
13 Jul 12 4:45 |
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?  Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP
|
|
If you have a look at the MSDN for datepart
http://msdn.microsoft.com/en-us/library/ms174420(v...
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 ----------------------------------------
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
---------------------------------------- |
|
Just to clarify - weekdays doesnt bring back all weekdays. It actually brings back the day of the week in numerical form (1-7). ----------------------------------------
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 (Programmer) |
13 Jul 12 4:58 |
I did say that I could see how to get weekdays, or non-weekdays. The problem is to tell Saturday from Sunday. Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP |
|
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...
Quote:
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. ----------------------------------------
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 (Programmer) |
13 Jul 12 7:00 |
Fine, thanks. Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP |
|
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:
Select * From sys.syslanguages
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:
CODEDeclare @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 |
|
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 ----------------------------------------
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,
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. |
|
@Qik3Coder
Good point. Star just for that from me.
D ----------------------------------------
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,
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 (Programmer) |
13 Jul 12 13:50 |
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! |
|
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 |
|
|
djj55 (Programmer) |
16 Jul 12 13:38 |
George, Thanks for the information. Although I should not have been running on the production server.  djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me! |
|
|
 |
|
Talk To Other Members
Read More Threads Like This One