Getting specific dates in SQL

[bellgodz]

I've been looking at some code posted here and have been trying to dissect all the date functions to see what they return but am still having trouble. I'm trying to get the date of the 1st thursday in a given month and if that date falss on either the 1st or 3rd, I need the date of the next thursday.

Example:
Month of May, the 1st thursday is 5/1/2008
I need the next thursday, which is 5/8/2008.

Thanks in advance,
Brian

[deepuv04]

hi,
I tried something which will give you all the dates comes on the give day.

My logic is i have taken the current date into @date,
2. Then taken @stardate as subtracted days from @date.
3. taken @enddate by adding one month to the @startdate.
4. takel the dates which has datename as given day between startdate and enddate.

 
declare @date datetime,
        @day int
 
declare @startdate datetime,
        @enddate datetime
set @date = getdate()
 
set @day = datepart(day,getDate())-1
 
set @startdate = DATEADD(day,-@day,getDate())
set @enddate =  DATEADD(month,1,@startdate)
 
SELECT Date 
FROM
    ( Select dateadd(day,number,@startdate) AS Date
      from master.dbo.spt_values
      where master.dbo.spt_values.type='p'
            AND dateadd(day,number,@startdate)<=(@enddate)
     ) AS T
WHERE DATENAME(dw, Date) = 'Thursday'
 
 
 

change the datename as 'Sunday', 'Monday' as per the requirement

thanks

[bellgodz]

Thanks, I think that may actually work. For my project I'm trying to automate the generation of a due date for bills. Since we pay our bills on thursdays I needed to get the next date for the next thursday. With a little modification I can use this to check for the next thursday if I pass it a date.

Thanks again.

hi,
I tried something which will give you all the dates comes on the give day.

My logic is i have taken the current date into @date,
2. Then taken @stardate as subtracted days from @date.
3. taken @enddate by adding one month to the @startdate.
4. takel the dates which has datename as given day between startdate and enddate.

 
declare @date datetime,
        @day int
 
declare @startdate datetime,
        @enddate datetime
set @date = getdate()
 
set @day = datepart(day,getDate())-1
 
set @startdate = DATEADD(day,-@day,getDate())
set @enddate =  DATEADD(month,1,@startdate)
 
SELECT Date 
FROM
    ( Select dateadd(day,number,@startdate) AS Date
      from master.dbo.spt_values
      where master.dbo.spt_values.type='p'
            AND dateadd(day,number,@startdate)<=(@enddate)
     ) AS T
WHERE DATENAME(dw, Date) = 'Thursday'
 
 
 

change the datename as 'Sunday', 'Monday' as per the requirement

thanks