Date function returning every wednesday of the year

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

    thx guys

  • 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

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • 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

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

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • excellent, thanks guys

  • 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

  • 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.

  • 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

  • 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.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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

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

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