Data Formatting
-
I need to capture data on certain events which occurred in the last year. I've used >=DATEADD(year,-1,getdate()) but this formula includes the current time and, confusingly, for users, excludes events earlier that day than the current time today. How do I include every event that day, one year ago? E.g. how can I generate '01/04/2003 00:00:00'?
-
I've always converted both sides of the date comparison to CONVERT(varchar,<date>,112) to give ISO date format YYYYMMDD which can be easily compared, e.g. in your example:
CONVERT(varchar,comparison_date,112) >= CONVERT(varchar,DATEADD(year,-1,GETDATE()),112)
But I'm not convinced as to the efficiency of this - I suppose it depends on the situation. Another possible way would be to use DATEDIFF but I've never actually tried it:
DATEDIFF(day,comparison_date,DATEADD(year,-1,GETDATE())) <= 0
If you take DATEDIFF(day,@date1,@date2) then, obviously, when @date1 = @date2 answer is 0, when @date1 < @date2 answer is positive and when @date1 > @date2 answer is negative.
In your example though this should also work:
DATEDIFF(day,comparison_date,GETDATE()) <= 365
not accounting for leap years of course.
Again not sure about the efficiencies of all these conversions so would be interested in any better methods.
-
Hello,
I thinkthat the easiest way to accomplish this would be to use
>=Dateadd(year, -1, Convert(varchar(8), Getdate(), 112))
The Convert gets rid of the time element, then the dateadd will make it a year ago. When dealing with dates Convert and Dataadd are my primary functions. As I write a lot of reports, I use these on an almost daily basis. I would suggest looking them up in the Books on Line and setting them to your favorites.
Chuck
-
Thanks. that does the job nicely.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply