Skip to main content
T
trojnfn
Inspiring
May 19, 2008
Question

sql dateadd question

  • May 19, 2008
  • 2 replies
  • 510 views
In my cfquery, I need to find all records that are
greater than one day old, so I use :

where datediff(d, date_arrived, getdate()) > 1

But this does not work, since I have a date arrived
record of 5/17/2008 and getdate is 5/18/2008, so
datediff should be 1 and the record should not show up on the report
but it does.

I also tried WHERE date_arrived >= #CreateODBCDate( DateAdd("d",-1,Now( )))# but this does not work either.


What am I doing wrong, or what is the proper way to do
this ?
    This topic has been closed for replies.

    2 replies

    D
    Dan_Bracuk
    Inspiring
    May 19, 2008
    To find out why your original query did not work, do this:

    select date_arrived, getdate(), datediff(d, date_arrived, getdate()) thediff
    from your table
    where something to identify the specific record

    cfdump the query. You should see your answer.
    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    May 19, 2008
    You're now in SQL land. Therefore the function you use on the left-hand side of a comparison, for example, datediff(), must be valid for the particular database brand.

    You could just do:

    where date_arrived >= #DateAdd("d",-1,Now())#



    A
    Anonymous
    May 19, 2008
    and just in general don't forget there might be differences in time on the database server vs the CF server
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices