Post reply

Why is the real date in SQL different than in EXCEL

  • March 4, 2014 at 4:35 am

    #396362

    Hi,

    I want to calculate with Dates and Times in SQL, but I have noticed that if I do

    Select CONVERT (real, Current_Timestamp), Current_timestamp

    gives me this

    41700.52 2014-03-04 12:31:29.830

    And in Excel it is

    41702.523/4/14 12:29

    As you can see, Excel is two days further when the number is not a date.

    What is the correct number for a date?

    Next question is, how can I convert a current time into a more accurate numeric value. As I also want to calculate with time

    example

    Select (1/(SUM(Rows)/1846415)) * (CURRENT_TIMESTAMP - '2014-03-04 10:51:00.380')

    does not work

    Kind regards

    Peter

  • March 4, 2014 at 6:50 am

    #1693872

    I think the difference is due to the leap year bug in Excel:

    http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

    Select (1/(SUM(Rows)/1846415)) * (CURRENT_TIMESTAMP - '2014-03-04 10:51:00.380')

    This won't work as CURRENT_TIMESTAMP is Oracle and SUM(Rows) is not possible without a FROM clause (and possible a GROUP BY).

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • March 5, 2014 at 1:04 am

    #1694177

    Thanks for this reply.

    The current_timestamp is from SQL, it work fine.

    And the query itself is part of a scripts, so the rows are already calculate.

    But anyway, I will see what I can do with the leapyear thingy, sounds plausible

  • March 5, 2014 at 1:10 am

    #1694179

    peter2501 (3/5/2014)

    The current_timestamp is from SQL, it work fine.

    Indeed it does, didn't even know it existed on SQL Server 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

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

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