May 22, 2011 at 5:12 pm
I am after these five dates, 21 months ago
Format MM/dd/yyyy
04/01/XXXX
07/01/XXXX
10/01/XXXX
01/01/XXXX (of the next year)
Only the above dates can be returned.
When date is 02/20/2011, then I would want 04/01/2009
When date is (today) 05/23/2011, then I would want 07/01/2009
When date is 07/10/2011, then I would want 10/01/2009
When date is 11/07/2011, then I would want 01/01/2010
The formula is take the current date, deduct 24 month, but then get the 1st date of the next quarter.
How do I do this with TSQL ???
Is it possible to get the date return in the format of MM/dd/yyyy as a varchar(12), Thanks:-)
May 22, 2011 at 5:33 pm
Something like this?
SELECT DATEADD(yy,-2,DATEADD(qq,DATEDIFF(qq,0,GETDATE())+1,0))
May 22, 2011 at 5:56 pm
Thanks Lutz :-):-D:-)
Awesome
May 22, 2011 at 6:03 pm
BTW that tricks works also for years, months and days (to cut off the time out of the date).
May 22, 2011 at 10:30 pm
Ooops I stuffed up the date selection, went forwardm should have gone backwards
Should be....
Format MM/dd/yyyy
01/01/XXXX
04/01/XXXX
07/01/XXXX
10/01/XXXX
Only the above dates can be returned.
When date is 02/20/2011, then I would want 01/01/2009
When date is (today) 05/23/2011, then I would want 04/01/2009
When date is 07/10/2011, then I would want 07/01/2009
When date is 11/07/2011, then I would want 10/01/2009
The formula is take the current date, deduct 24 month, but then get the 1st date of the PREVIOUS quarter.
Sorry about that, had a fiddle with the code posted, not getting the result I need. any ideas, thanks:-)
May 23, 2011 at 1:16 am
Just a little modification in above query and get the result :
SELECT CONVERT(VARCHAR(12),DATEADD(yy,-2,DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0)),101)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply