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"

asked 07 Dec '12, 17:38

sybasetester's gravatar image

sybasetester
513310
accept rate: 0%


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 :-)

permanent link

answered 07 Dec '12, 18:22

Mark%20Culp's gravatar image

Mark Culp
23.2k9132272
accept rate: 40%

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):

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...

(11 Dec '12, 04:03) Volker Barth
showing 1 of 8 show all flat view

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

permanent link

answered 09 Dec '12, 10:15

Breck%20Carter's gravatar image

Breck Carter
26.2k430600865
accept rate: 20%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×22
×15
×4

question asked: 07 Dec '12, 17:38

question was seen: 2,025 times

last updated: 11 Dec '12, 04:04