By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,363 Members | 2,473 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,363 IT Pros & Developers. It's quick & easy.

Getting specific dates in SQL

P: 3
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
Jun 25 '08 #1
Share this Question
Share on Google+
2 Replies


deepuv04
Expert 100+
P: 227
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. declare @date datetime,
  3.         @day int
  4.  
  5. declare @startdate datetime,
  6.         @enddate datetime
  7. set @date = getdate()
  8.  
  9. set @day = datepart(day,getDate())-1
  10.  
  11. set @startdate = DATEADD(day,-@day,getDate())
  12. set @enddate =  DATEADD(month,1,@startdate)
  13.  
  14. SELECT Date 
  15. FROM
  16.     ( Select dateadd(day,number,@startdate) AS Date
  17.       from master.dbo.spt_values
  18.       where master.dbo.spt_values.type='p'
  19.             AND dateadd(day,number,@startdate)<=(@enddate)
  20.      ) AS T
  21. WHERE DATENAME(dw, Date) = 'Thursday'
  22.  
  23.  
  24.  
change the datename as 'Sunday', 'Monday' as per the requirement

thanks
Jun 25 '08 #2

P: 3
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. declare @date datetime,
  3.         @day int
  4.  
  5. declare @startdate datetime,
  6.         @enddate datetime
  7. set @date = getdate()
  8.  
  9. set @day = datepart(day,getDate())-1
  10.  
  11. set @startdate = DATEADD(day,-@day,getDate())
  12. set @enddate =  DATEADD(month,1,@startdate)
  13.  
  14. SELECT Date 
  15. FROM
  16.     ( Select dateadd(day,number,@startdate) AS Date
  17.       from master.dbo.spt_values
  18.       where master.dbo.spt_values.type='p'
  19.             AND dateadd(day,number,@startdate)<=(@enddate)
  20.      ) AS T
  21. WHERE DATENAME(dw, Date) = 'Thursday'
  22.  
  23.  
  24.  
change the datename as 'Sunday', 'Monday' as per the requirement

thanks
Jun 26 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.