Date function returning every wednesday of the year

  • Denby

    Hall of Fame

    Points: 3120

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

    thx guys

  • Jonathan Kehayias

    One Orange Chip

    Points: 26672

    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]

  • Jack Corbett

    SSC Guru

    Points: 184359

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • Jonathan Kehayias

    One Orange Chip

    Points: 26672

    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]

  • Denby

    Hall of Fame

    Points: 3120

    excellent, thanks guys

  • Jack Corbett

    SSC Guru

    Points: 184359

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • Denby

    Hall of Fame

    Points: 3120

    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: 184359

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • ChrisM@Work

    SSC Guru

    Points: 186043

    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