|
How can I convert from DateTime to Gregorian? I need the reverse of this: --Convert from Gregorian to DateTime: select DATEFORMAT(cast(UTCBigintToNOMTime(135728021882620000) as DATETIME), 'yyyy-mm-dd hh:mm:ss') as myDateTime; Result: "2012-11-21 08:49:48" |
|
It is going to be something like: select datediff( second, @gregorian_day0, '2012-11-21 08:49:48' ) for some @gregorian_day0 - perhaps '01-01-01 00:00:00'? .. but due to the date shift in 1563 I'm not sure what day0 would be (the computation is left to the reader :-) According to Wiki the first day of the Gregorian calendar is Friday, 15 October 1582 link text These returns results --DAYS: select CAST(datediff( day, 'Oct 15 1582' , 'Nov 21 2012 08:49:48') AS CHAR) as myDATETIME --YEARS: select CAST(datediff( year, 'Oct 15 1582', 'Nov 21 2012 08:49:48') AS CHAR) as myDATETIME Why does this not return a result? --SECONDS: select CAST(datediff( second, 'Oct 15 1582', 'Nov 21 2012 08:49:48') AS CHAR) as myDATETIME
(10 Dec '12, 12:18)
sybasetester
Replies hidden
select CAST(datediff( day, 'Oct 15 1582' , 'Nov 21 2012 08:49:48') AS CHAR) as myDATETIME; myDATETIME '157092' select CAST(datediff( year, 'Oct 15 1582', 'Nov 21 2012 08:49:48') AS CHAR) as myDATETIME; myDATETIME '430' select CAST(datediff( second, 'Oct 15 1582', 'Nov 21 2012 08:49:48') AS CHAR) as myDATETIME; myDATETIME '13572780588'
(10 Dec '12, 13:37)
Breck Carter
Breck, I get this error message: Value datediff(second,1582-10-15 00:00:00.000000,2012-11-21 08:49:48.000000) out of range for destination
(10 Dec '12, 14:08)
sybasetester
What is the exact SQL statement that you are running to see this message? Are you really trying to store the result in a variable? If so, what is the variable type? The following statement also works for myself: select @@VERSION, CAST(datediff( second, '1582-10-15 00:00:00.000000', '2012-11-21 08:49:48.000000') AS CHAR) as myDATETIME @@VERSION,myDATETIME '12.0.1.3797','13572780588'
(10 Dec '12, 14:52)
Jeff Albion
This gives a result: select @@VERSION, CAST(datediff( day, '1582-10-15 00:00:00.000000', '2012-11-21 08:49:48.000000') AS CHAR) as myDATETIME @@VERSION myDATETIME 11.0.1.2645 157092 This doesn't: select @@VERSION, CAST(datediff( second, '1582-10-15 00:00:00.000000', '2012-11-21 08:49:48.000000') AS CHAR) as myDATETIME
(10 Dec '12, 15:00)
sybasetester
I believe the problem stems from the fact that datediff returns a 32bit integer and the number of seconds in 157092 days is too large to fit into an integer! :-( i.e. 157092*86400 > 2^31 ... which is basically what Breck said 2 hours ago. sadly, I checked the code and the calculation is done using 64bit integers but the routine is defined to return a signed 32bit int so the value overflows the maximum hence the "out of range for destination error" that is raised
(10 Dec '12, 17:01)
Mark Culp
So we need to upgrade from version 11.0.1.2645 to 12.0.1.3797? Or is there some way to cast to other data types for the calculation to work?
(10 Dec '12, 18:50)
sybasetester
From the v12 docs (SQL Anywhere behavior changes):
That sound like a reason to upgrade...
(11 Dec '12, 04:03)
Volker Barth
|
|
Um, November 21 2012 is a Gregorian date... what exactly are you asking? |
