Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Why is the real date in SQL different than in EXCEL Expand / Collapse
Author
Message
Posted Tuesday, March 4, 2014 4:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 4:42 AM
Points: 16, Visits: 40
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.52 3/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

Post #1547241
Posted Tuesday, March 4, 2014 6:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:46 AM
Points: 13,646, Visits: 11,524
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).




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1547306
Posted Wednesday, March 5, 2014 1:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 4:42 AM
Points: 16, Visits: 40
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
Post #1547660
Posted Wednesday, March 5, 2014 1:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:46 AM
Points: 13,646, Visits: 11,524
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




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1547663
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse