Post reply

Data Formatting

  • April 1, 2004 at 1:38 am

    #160795

    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'?

  • April 1, 2004 at 2:39 am

    #501412

    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.

  • April 1, 2004 at 3:07 am

    #501415

    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

  • April 1, 2004 at 7:13 am

    #501432

    Thanks. that does the job nicely.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply