Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jul 2006
    Posts
    2

    Unanswered: How to find list of all Mondays in 2006

    I have a one problem in my sql server. I would like to find out the list of all Monday's dates in the year 2006. Can anybody help me out?.

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    declare @indate datetime
    set @indate = getdate()

    select
    case cast(datediff(d,'1/1/1900',@indate) % 7
    as int)
    when 0 then 'Monday'
    when 1 then 'Tuesday'
    when 2 then 'Wednesday'
    when 3 then 'Thursday'
    when 4 then 'Friday'
    when 5 then 'Saturday'
    when 6 then 'Sunday'
    end as DOW

    this will work you just need to cursor through the year

    or you could use a calendar table and use this in the where clause
    Last edited by rbackmann; 07-03-06 at 14:16.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Cursor? Yeck.
    Code:
    set nocount on
    declare	@TargetYear datetime
    set	@TargetYear = '01-01-2006'
    
    declare	@SequentialValues table (SequenceValue int)
    declare	@SeedValue int
    set	@SeedValue = 0
    
    while	@SeedValue < 52
    	begin
    		insert into @SequentialValues (SequenceValue) values(@SeedValue)
    		set @SeedValue = @SeedValue + 1
    	end
    
    select	dateadd(day, -(datepart(dw, @TargetYear) + 7) % 7 + 2, @TargetYear) + (SequenceValue * 7)
    from	@SequentialValues
    where	year(dateadd(day, -(datepart(dw, @TargetYear) + 7) % 7 + 2, @TargetYear) + (SequenceValue * 7)) = year(@TargetYear)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    when modulo year / 7 = 3 there are 53 mondays in the year you will need to account for that
    WRONG forgot leap year.

    How to get the 53rd Monday. ????


    ahh summer school
    Last edited by rbackmann; 07-03-06 at 14:50.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Bump the sequential values up to at least 32. Ideally, you should have a table of sequential values in your database anyway, populated up to a thousand or so. It greatly simplifies many queries involving date ranges.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    Ideally, you should have a table of sequential values in your database anyway, populated up to a thousand or so. It greatly simplifies many queries involving date ranges.
    A right boon. I just ran this (actually, Blindman very indirectly referred me to it):
    http://sqljunkies.com/WebLog/amachan...bersTable.aspx

    I would agree that it is useful and easy. I think he overdoes it when he describes it as fun
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pootie, i'm surprised you only just now clued to the numbers table

    see this post
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You didn't mention which version of SQL Server you are using, so I'll give a generic answer that works for at least SQL 6.5 through SQL 2005. There are cleaner versions that are version specific, but this is portable.
    Code:
    DECLARE @iYear		INT
    
    SET @iYear = 2006
    
    SELECT DateAdd(week, o1.v + o0.v, DateAdd(day, 2 - DatePart(dw
    ,  Convert(VARCHAR(4), @iYear) + '-01-01'), Convert(VARCHAR(4)
    ,  @iYear) + '-01-01'))
       FROM (SELECT 0 AS v UNION SELECT 8 UNION SELECT 16 UNION SELECT 24
          UNION SELECT 32 UNION SELECT 40 UNION SELECT 48) AS o1
       CROSS JOIN (SELECT 0 AS v UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
          UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) AS o0
       WHERE @iYear = DatePart(year, DateAdd(week, o1.v + o0.v, DateAdd(day
    ,     2 - DatePart(dw, Convert(VARCHAR(4), @iYear) + '-01-01')
    ,     Convert(VARCHAR(4), @iYear) + '-01-01')))
       ORDER BY 1
    -PatP

  9. #9
    Join Date
    Jun 2005
    Posts
    50
    set noCount on
    Declare @lcl_date datetime
    ,@diff int
    ,@current_date datetime
    Select @lcl_date = '2006/01/01'

    Select @diff = case (datepart(dw,@lcl_date))
    when 1 then 1
    when 2 then 0
    when 3 then -1
    when 4 then -2
    when 5 then -3
    when 6 then -4
    when 7 then -5
    end

    Select @diff

    -- 1st monday of the year
    Select @current_date = dateadd(dd,@diff,@lcl_date)
    Select @diff = 7

    Select @current_date

    while (dateadd(dd,@diff,@current_date) <= '2006/12/31')
    begin
    Select dateadd(dd,@diff,@current_date)
    Select @current_date = dateadd(dd,@diff,@current_date)
    end




    set noCount off

  10. #10
    Join Date
    Jun 2005
    Posts
    50
    just comment out

    Select @diff
    i got confused wen i tested the code for year%3 gives 3 for 53 mondays...
    i got 54 recds cos of the select @diff...

    u can test for 2005/01/01 for 53 mondays

  11. #11
    Join Date
    Jun 2005
    Posts
    50
    Pat Phelan
    ur code gives 52 mondays for 2005

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    pootie, i'm surprised you only just now clued to the numbers table

    see this post
    Lol. Apols Rudy - there was a degree of ambiguity to my post. I meant I "merely" ran the code (i.e. having a numbers table is merely one F5 press away) rather than I had just recently run it. I don't know how I ever lived without my numbers table. It must have taken you sometime finding that old post though
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by dbaguru
    Pat Phelan
    ur code gives 52 mondays for 2005
    Pat's code is working excellent,and in my calender of 2005 I found 52 Mondays only ...Check out 1996 it contains 53 mondays
    then 2001,2007, 2012 , 2024 ..... all have 53 Mondays
    Last edited by rudra; 07-04-06 at 04:43.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by dbaguru
    Pat Phelan
    ur code gives 52 mondays for 2005
    How many were you hoping for?

    -PatP

  15. #15
    Join Date
    Jun 2005
    Posts
    50
    my mistake Pat...

    my code shud have been


    set noCount on
    Declare @lcl_date datetime
    ,@diff int
    ,@current_date datetime
    Select @lcl_date = '2005/01/01'

    Select @diff = case (datepart(dw,@lcl_date))
    when 1 then 1
    when 2 then 0
    when 3 then 6
    when 4 then 5
    when 5 then 4
    when 6 then 3
    when 7 then 2
    end

    ---Select @diff

    -- 1st monday of the year
    Select @current_date = dateadd(dd,@diff,@lcl_date)
    Select @diff = 7

    Select @current_date

    while (dateadd(dd,@diff,@current_date) <= '2005/12/31')
    begin
    Select dateadd(dd,@diff,@current_date)
    Select @current_date = dateadd(dd,@diff,@current_date)
    end




    set noCount off

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •