How do you convert a DateTime to Gregorian?

 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 51●3●3●10 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 :-) answered 07 Dec '12, 18:22 Mark Culp 23.4k●9●133●275 accept rate: 41% 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? answered 09 Dec '12, 10:15 Breck Carter 27.1k●455●621●894 accept rate: 21%
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• 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
×6

question asked: 07 Dec '12, 17:38

question was seen: 2,348 times

last updated: 11 Dec '12, 04:04