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

How to get Last Thursday Date

rsrinivasan
100+
P: 221
Hi all,

I need a query that will return last thursday date dynamically. Can any one send it to me?

Thanks in advance,
Jun 26 '08 #1
Share this Question
Share on Google+
1 Reply


deepuv04
Expert 100+
P: 227
Hi,
If you are looking for the last Thursday
Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3.  
  4. declare @date datetime,
  5.         @day int
  6.  
  7.  
  8.  
  9. declare @startdate datetime,
  10.  
  11.         @enddate datetime
  12.  
  13. --set @date = getdate()
  14.  
  15.  
  16.  
  17. set @day = datepart(day,getDate())-1
  18.  
  19.  
  20.  
  21. set @startdate = DATEADD(day,-30,getDate())
  22.  
  23. set @enddate =  getdate()
  24.  
  25.  
  26.  
  27. SELECT TOP 1 Date
  28.  
  29. FROM
  30.  
  31.     ( Select dateadd(day,number,@startdate) AS Date
  32.  
  33.       from master.dbo.spt_values
  34.  
  35.       where master.dbo.spt_values.type='p'
  36.  
  37.             AND dateadd(day,number,@startdate)<=(@enddate)
  38.  
  39.      ) AS T
  40.  
  41. WHERE DATENAME(dw, Date) = 'Thursday' AND
  42.       Date <= GetDate() 
  43. ORDER BY Date desc
  44.  
If you are looking for last Thursday in a month:

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3.  
  4. declare @date datetime,
  5.         @day int
  6.  
  7.  
  8.  
  9. declare @startdate datetime,
  10.  
  11.         @enddate datetime
  12.  
  13. set @date = getdate()
  14.  
  15.  
  16.  
  17. set @day = datepart(day,@date)-1
  18.  
  19.  
  20.  
  21. set @startdate = DATEADD(day,-@day,@date)
  22.  
  23. set @enddate =  DATEADD(month,1,@startdate)
  24.  
  25.  
  26.  
  27. SELECT TOP 1 Date
  28.  
  29. FROM
  30.  
  31.     ( Select dateadd(day,number,@startdate) AS Date
  32.  
  33.       from master.dbo.spt_values
  34.  
  35.       where master.dbo.spt_values.type='p'
  36.  
  37.             AND dateadd(day,number,@startdate)<=(@enddate)
  38.  
  39.      ) AS T
  40.  
  41. WHERE DATENAME(dw, Date) = 'Thursday' 
  42.  
  43. ORDER BY Date desc
  44.  
thanks
Jun 26 '08 #2

Post your reply

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