Post reply

date value diffrerence

  • June 22, 2010 at 8:53 am

    #224123

    Hi

    Do you know why these two give a different result?

    select ((DateDiff("d",'01/01/1970', getdate())) * 1)

    select ((DateDiff("d",'01/01/1970', cast(getdate() as numeric) )) * 1)

    Thanks

  • June 22, 2010 at 9:20 am

    #1183410

    select ((DateDiff("d",'01/01/1970', getdate())) * 1)

    select ((DateDiff("d",'01/01/1970', cast(getdate() as numeric) )) * 1)

    Results:

    (No column name)

    14782

    (No column name)

    14782

    When running on my SQL Server 2008 as you can see there is NOT a difference in the values returned.

    Can you post the values returned by your running of the 2 SQL statements?

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • June 22, 2010 at 9:24 am

    #1183415

    i get 14782 for the first and 14783 for the second

  • June 22, 2010 at 9:24 am

    #1183416

    and im on 2008

  • June 22, 2010 at 10:26 am

    #1183472

    This shows what is happening:

    select

    BeforeNoon= cast(convert(datetime,'20100622 11:59:59.997') as numeric),

    AfterNoon= cast(convert(datetime,'20100622 12:00:00.000') as numeric)

    Results:

    BeforeNoon AfterNoon

    -------------------- --------------------

    40349 40350

    (1 row(s) affected)

    This works the same in SQL 2000, 2005, and 2008.

    A better approach is for you to explain what you are trying to do so that we can suggest solutions.

  • June 22, 2010 at 10:49 am

    #1183487

    My former post was run PRIOR TO NOON. Following up on Michael Valentine Jones forum post ... running at

    2010-06-22 12:41:59.030 or after noon gives

    14782 and 14783.

    Michael Valentine Jones - thanks for enlightening myself as welll as the OP.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • June 23, 2010 at 2:30 am

    #1183785

    Thanks for the answers

    So the conclusion is, if i use this method in a query it could return different results when run in the morning to when run in the afternoon of the same day

  • June 24, 2010 at 7:34 am

    #1184490

    Do it this way so that you don't have to worry about that problem:

    select datediff(dd,'19700101',getdate())

    Much more info about handling datetime in SQL Server here:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

  • June 25, 2010 at 5:52 am

    #1184932

    It's because of rounding, run this:

    SELECT CAST (GETDATE() AS numeric), CAST(GETDATE() AS NUMERIC(28,12))

    Nigel
    How to post questions

Viewing 9 posts - 1 through 8 (of 8 total)

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