Date function returning every wednesday of the year

Question

Post reply

Date function returning every wednesday of the year

Denby

Hall of Fame

Points: 3120
More actions
December 19, 2008 at 7:10 am

#128759

Need help for a date function to return every wednesday of the year.
thx guys

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

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

This is the kind of problem that a calendar table is good at solving, and not a function.

http://www.sqlservercentral.com/articles/Advanced+Querying/onthetrailoftheisoweek/1675/

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

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

I agree with Jonathan. Here's a basic idea of what you would want to do:

[font="Courier New"]SELECT TOP 366

IDENTITY(INT, 1, 1) AS n

INTO

#nums

FROM

sys.all_objects

SELECT

DATEADD(DAY, n, '1/1/2008')

FROM

#nums

WHERE

DATEPART(weekday, DATEADD(DAY, n, '1/1/2008')) = CASE @@DateFirst

WHEN 7 THEN 4

WHEN 6 THEN 5

WHEN 5 THEN 6

WHEN 4 THEN 7

WHEN 3 THEN 1

WHEN 2 THEN 2

ELSE 3

END

DROP TABLE #nums[/font]

If you already have a numbers/tally table then you can replace the temp table with it. DatePart is based on the Set DateFirst option and by having the Case in the where you handle all the DateFirst options.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

I would have missed that @@datefirst part. Nice example Jack.

Denby Hall of Fame Points: 3120 More actions · Answer 4

Denby

Hall of Fame

Points: 3120

December 19, 2008 at 7:32 am

#915282

excellent, thanks guys

Jack Corbett SSC Guru Points: 184393 More actions · Answer 5

Jonathan Kehayias (12/19/2008)
I would have missed that @@datefirst part. Nice example Jack.

Thanks, I only got it because I had to lookup what number was returned for Wednesday and knew I'd end up getting burned by a different setting.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Denby Hall of Fame Points: 3120 More actions · Answer 6

Just to follow-up guys trying to assing values to variables:

example:

set @quart = (select DATEPART(Quarter ,Date)) from @nums where Date = convert(varchar(50),getdate(), 103))

but gettin the following error

Msg 242, Level 16, State 3, Line 33

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Jack Corbett SSC Guru Points: 184393 More actions · Answer 7

Your SQL Server is set to recognize dates in mdy and the parameter you have on your convert is producing dmy so when sql server is converting it back to a date you are getting the conversion error. You want 112 is ISO and works with all data format settings.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Jack Corbett (12/19/2008)
I agree with Jonathan. Here's a basic idea of what you would want to do:
[font="Courier New"]SELECT TOP 366
   IDENTITY(INT, 1, 1) AS n
INTO
   #nums
FROM
   sys.all_objects

   SELECT
       DATEADD(DAY, n, '1/1/2008')
   FROM
       #nums
   WHERE
       DATEPART(weekday, DATEADD(DAY, n, '1/1/2008')) = CASE @@DateFirst
                                                               WHEN 7 THEN 4
                                                               WHEN 6 THEN 5
                                                               WHEN 5 THEN 6
                                                               WHEN 4 THEN 7
                                                               WHEN 3 THEN 1
                                                               WHEN 2 THEN 2
                                                               ELSE 3

END
DROP TABLE #nums[/font]
If you already have a numbers/tally table then you can replace the temp table with it.
DatePart is based on the Set DateFirst option and by having the Case in the where you handle all the DateFirst options.

This is the sort of simple, effective and fast code which makes you think "reckon I'll start a blog"

- not so other people can see it, but as a library of the good stuff that you can find from anywhere.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden