Post reply

retrieve current time

  • July 22, 2010 at 2:36 pm

    #72054

    I have a table has a datetime field stored date, e.g. 2010-07-21, and another datetime field store time, e.g 23:00, i need to get the rows out for those not within current timestamp last 24 hours, say if the time now is 2010/7/22/15:30, i need to retreive those rows has the field date marked before 2010/7/21/15:30. What would be the best way? I am thinking retrieve current time, and compare with the time column on the table, how to do that? thank you

  • July 22, 2010 at 2:57 pm

    #1197205

    Are you sure both columns are datetime?

    I think both are char/varchar, since it's unlikely to store the data the way you menitoned in a datetime column....

    To be sure the solution I have in mind is what you're looking for please post table definition and sample data together with expected result.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • July 22, 2010 at 3:00 pm

    #1197207

    The first condition will allow use of an index on the Date_Column, and the second will compare the combined date and time columns to 24 hours before current time for an exact compare.

    where

    -- Date >= yesterday at 00:00:00.000

    Date_Column >= dateadd(dd,datediff(dd,0,getdate())-1,0)and

    -- -- Date+Time colums >= one day before current datetime

    Date_Column+Time_Column >= dateadd(dd,-1,getdate())

    This code assumes that both columns are data type DATETIME.

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

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