How do you convert a DateTime to Gregorian?

[Former Member]

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"

[VolkerBarth]

This might be asked in the wrong forum - this forum deals with the SAP SQL Anywhere database, whereas your question seems related to SAP B4/HANA, right?

[VolkerBarth]

Well, this forum is not at all about the SAP ABAP programming environment but the SAP SQL Anywhere database management system, which does not offer ABAP interfaces.

[Breck_Carter]

Um, November 21 2012 is a Gregorian date... what exactly are you asking?

[MarkCulp]

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 🙂

[Former Member]

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

[Breck_Carter]

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'

[Former Member]

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

[jeff_albion]

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'

[Former Member]

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

[MarkCulp]

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

[Former Member]

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?

[VolkerBarth]

From the v12 docs (SQL Anywhere behavior changes😞

DATEDIFF function
In previous releases, the DATEDIFF function returned an INTEGER for date parts of hours and smaller. DATEDIFF now returns an a BIGINT for these date parts. See DATEDIFF function Date and time.

That sound like a reason to upgrade...