[thelist] Free Tip: SQL Date Query Freebiees

[Rob Smith]

<tip type="Useful SQL Date Query Freebies" author="Rob.Smith">

 

SELECT convert(varchar,dateadd(dd,-1,getdate()),101) as Yesterday,
convert(varchar,getdate(),101) as Today,
convert(varchar,dateadd(dd,1,getdate()),101) as Tomorrow

Select convert(varchar,dateadd(dd,-7,DATEADD(wk,
DATEDIFF(wk,0,getdate()), 0)),101) as [Last Monday],
convert(varchar,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0),101) as Monday,
convert(varchar,dateadd(dd,7,DATEADD(wk, DATEDIFF(wk,0,getdate()),
0)),101) as [Next Monday]

select convert(varchar,dateadd(mm,-1,DATEADD(mm,
DATEDIFF(mm,0,getdate()), 0)),101) as [First Day of Last Month],
convert(varchar,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0),101) as [First
Day of Month],   convert(varchar,dateadd(mm,1,DATEADD(mm,
DATEDIFF(mm,0,getdate()), 0)),101) as [First Day of Next Month]

select convert(varchar,dateadd(qq,-1,DATEADD(qq,
DATEDIFF(qq,0,getdate()), 0)),101) as [First Day of Last Quarter],
convert(varchar,DATEADD(qq, DATEDIFF(qq,0,getdate()), 0),101) as [First
Day of Quarter],convert(varchar,dateadd(qq,1,DATEADD(qq,
DATEDIFF(qq,0,getdate()), 0)),101) as [First Day of Next Quarter]

select convert(varchar,dateadd(yy,-1,DATEADD(yy,
DATEDIFF(yy,0,getdate()), 0)),101) as [First Day of Last Year],
convert(varchar,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),101) as [First
Day of Year],    convert(varchar,dateadd(yy,1,DATEADD(yy,
DATEDIFF(yy,0,getdate()), 0)),101) as [First Day of Next Year]

SELECT convert(varchar,dateadd(yy,-1,getdate()),101) as [Today (LY)]

select convert(varchar,DATEADD(wk,
DATEDIFF(wk,0,dateadd(yy,-1,getdate())), 0),101) as [Monday (LY)],
convert(varchar,dateadd(dd,7,DATEADD(wk,
DATEDIFF(wk,0,dateadd(yy,-1,getdate())), 0)),101) as [Next Monday (LY)]

Select convert(varchar,DATEADD(mm,
DATEDIFF(mm,0,dateadd(yy,-1,getdate())), 0),101) as [First Day of Month
(LY)],     convert(varchar,dateadd(mm,1,DATEADD(mm,
DATEDIFF(mm,0,dateadd(yy,-1,getdate())), 0)),101) as [First Day of Next
Month (LY)]

SELECT convert(varchar,DATEADD(qq,
DATEDIFF(qq,0,dateadd(yy,-1,getdate())), 0),101) as [First Day of
Quarter (LY)],   convert(varchar,dateadd(qq,1,DATEADD(qq,
DATEDIFF(qq,0,dateadd(yy,-1,getdate())), 0)),101) as [First Day of Next
Quarter (LY)]

 

Runs in MS SQL

</tip>


Rob Smith
LexJet
rob.smith at lexjet.com
http://www.lexjet.com
(800)453-9538
(941)330-1210 Int'l
(941)330-1220 Fax
1680 Fruitville Road, 3rd Floor
Sarasota, FL 34236