Hi all,
I need a query that will return last thursday date dynamically. Can any one send it to me?
Thanks in advance,
I need a query that will return last thursday date dynamically. Can any one send it to me?
Thanks in advance,
-
-
Hi,
If you are looking for the last Thursday
[code= sql]
declare @date datetime,
@day int
declare @startdate datetime,
@enddate datetime
--set @date = getdate()
set @day = datepart(day,ge tDate())-1
set @startdate = DATEADD(day,-30,getDate())
set @enddate = getdate()
SELECT TOP 1 Date
FROM
( Select dateadd(day,num ber,@startdate) AS Date
from master.dbo.spt_ values
where master.dbo.spt_ values.type='p'
AND dateadd(day,num ber,@startdate) <=(@enddate)
) AS T
WHERE DATENAME(dw, Date) = 'Thursday' AND
Date <= GetDate()
ORDER BY Date desc
[/code]
If you are looking for last Thursday in a month:
[code=sql]
declare @date datetime,
@day int
declare @startdate datetime,
@enddate datetime
set @date = getdate()
set @day = datepart(day,@d ate)-1
set @startdate = DATEADD(day,-@day,@date)
set @enddate = DATEADD(month,1 ,@startdate)
SELECT TOP 1 Date
FROM
( Select dateadd(day,num ber,@startdate) AS Date
from master.dbo.spt_ values
where master.dbo.spt_ values.type='p'
AND dateadd(day,num ber,@startdate) <=(@enddate)
) AS T
WHERE DATENAME(dw, Date) = 'Thursday'
ORDER BY Date desc
[/code]
thanks
Comment