Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2006
    Posts
    8

    Unanswered: Date function help

    Hi,

    I need to write a date function in a stored procedure that finds the first Monday of every month following the first Saturday of that month (date to beused as a parameter for monthly reporting purposes).

    Can anyone help with this?
    Thanks,
    Colin

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1

    first Monday after first Saturday

    Code:
    declare @year int, @month int
    select @year=2006, @month=7
    select 'FirstSaturday'=dateadd(dd
    ,7-datepart(dw,convert(varchar(4),@year)+'-'+convert(varchar(2),@month)+'-01')
    ,convert(varchar(4),@year)+'-'+convert(varchar(2),@month)+'-01')
    ,'MondayFolowing'=dateadd(dd
    ,9-datepart(dw,convert(varchar(4),@year)+'-'+convert(varchar(2),@month)+'-01')
    ,convert(varchar(4),@year)+'-'+convert(varchar(2),@month)+'-01')

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Private message to PDreyer
    Quote Originally Posted by Colin4
    Thanks for responding. I'm going to try this out when I get to work. I'm wondering if this will work as a generic function though, to be a permanent fixture in the stored procedure for use through the entire year...
    I'm still getting used to date functions in stored procedures and trying to understand them better.

    I don't think I would be able to set:
    select @year=2006, @month=7 as the stored proc needs to run for the entire year.

    Basically I need it to calcuate what the first Monday is for each based after the first Saturday, that being different for each month of the year. Then I use whatever the outcome of that is as my date parameter to plug into my stored procedure.

    Any thoughts?

    Colin
    I suggest you post replies in the forum. How else will others know if the suggested solution solved your problem.

    To generate a list for a specific year you can generate a temp table with all the months e.g.
    Code:
    declare @year_char char(4)
    set @year_char='2006'
    select day1=convert(datetime,@year_char+'-01-01') into #t1 union all
    select day1=convert(datetime,@year_char+'-02-01') union all
    select day1=convert(datetime,@year_char+'-03-01') union all
    select day1=convert(datetime,@year_char+'-04-01') union all
    select day1=convert(datetime,@year_char+'-05-01') union all
    select day1=convert(datetime,@year_char+'-06-01') union all
    select day1=convert(datetime,@year_char+'-07-01') union all
    select day1=convert(datetime,@year_char+'-08-01') union all
    select day1=convert(datetime,@year_char+'-09-01') union all
    select day1=convert(datetime,@year_char+'-10-01') union all
    select day1=convert(datetime,@year_char+'-11-01') union all
    select day1=convert(datetime,@year_char+'-12-01')
    update #t1 set day1=dateadd(dd,9-datepart(dw,day1),day1)
    select * from #t1
    A more generic way to find any occurrence of any day in any month will be
    Code:
    declare @year int, @month int
    ,@reqday char(3), @occr int
    select @year=2006, @month=7
    ,@reqday='Mon'
    ,@occr=2 -- occurance 1,2,etc. e.g. 2 for 2nd monday
    select dateadd(dd,(@occr-1)*7+(7
    +case when @reqday='Sun' then 1
          when @reqday='Mon' then 2
          when @reqday='Tue' then 3
          when @reqday='Wed' then 4
          when @reqday='Thu' then 5
          when @reqday='Fri' then 6
          when @reqday='Sat' then 7
          else null
    end
    -datepart(dw,day1))%7,day1)
    from (select day1=convert(varchar(4),@year)+'-'+convert(varchar(2),@month)+'-01') v1
    You can then add the number of days to follow this date

  4. #4
    Join Date
    Jul 2006
    Posts
    8
    Thanks for the examples. I'm still having a problem if I declare specific parameters though. The reporting is auomated and the date functionality needs to be hard-coded in. Is there a way to use the getdate() function to generate the first Monday following first Saturday?
    Colin

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Well you can just replace @year with year(getdate()) and @month with month(getdate())
    Or:
    Code:
    select 'MonAfter1stSat'=dateadd(dd,9
    -datepart(dw,dateadd(dd,1-datepart(dd,getdate()),getdate())) -- dw of 1st
    ,dateadd(dd,1-datepart(dd,getdate()),getdate()) -- the 1st
    )

  6. #6
    Join Date
    Jul 2006
    Posts
    8
    Excellent thanks!

Posting Permissions

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