September 14, 2012 at 9:37 am
Ok i have been looking at this for a while and i need some help i cant seem to figure it out.
I need to run a query selecting data for ONE (possibly 2) year(s). I have data dating back all the way to 2002. i just want the most recent year or possibly two.
the date format i have right now is just 201206 (june 2012) I want to be able to go back 12 months (or 24) and not have to keep changing the code. This includes the current month.
So i figure i would have to use BETWEEN and GETDATE and part of the getdate would be (getdate()-12) (or 24) or something like that.
Am i on the right track? or is there another way to do this?
I want to make this as user friendly as possible...aka no maintenance.
ANY help would be greatly appreciated. I am just completely lost right now.
September 14, 2012 at 9:43 am
You can use:
WHERE Date > dateadd(yy, -1, getdate())
if you're using a DateTime field.
or
WHERE Date > CONVERT(Varchar(6), dateadd(yy, -1, getdate()), 112)
for "201209" format...
Note these are not very efficient for large data quantities because they disable index use.
You can try setting up a variable to hold the value & using that in the WHERE clause.
September 14, 2012 at 10:13 am
Ok awesome it seems to have worked.
just wondering what is the 112 doing at the end of the second code? I am just curious what the code is doing. (i swear curiosity will kill me one day:-))
September 14, 2012 at 10:18 am
It specifies the format of the convert (see help or Books On Line). Format 112 is YYYYMMDD so converting it to Varchar(6) just 'loses' the day. There are loads of other formats, but the 100 series are generally the same as <100, but with 4-character years.
Curiosity is a good thing in SQL!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply