View Revisions: Issue #4261
| Summary | 0004261: Oracle: Need to handle negative years or BC years | ||
|---|---|---|---|
| Revision | 2020-01-23 17:30 by pmarzullo | ||
| Description | Oracle dialect needs to handle negative years, or BC years. Conversion from timestamp to char for transport to target system: select to_char(to_timestamp('-3546-02-13 12:00:00','SYYYY-MM-DD HH24:MI:SS'), 'SYYYY-MM-DD HH24:MI:SS') from dual; select to_char(to_timestamp('-3546-02-13 12:00:00','SYYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS AD') from dual; select to_char(to_timestamp('3546-02-13 12:00:00 BC','YYYY-MM-DD HH24:MI:SS BC'), 'SYYYY-MM-DD HH24:MI:SS') from dual; select to_char(to_timestamp('3546-02-13 12:00:00 BC','YYYY-MM-DD HH24:MI:SS BC'), 'YYYY-MM-DD HH24:MI:SS AD') from dual; The SYYYY means signed years. The AD indicates BC or AD as appropriate. I would suggest going with SYYYY on parsing and formatting. DBCompare also needs to be looked at, since milliseconds since EPOCH is used for comparison, and this negative date does not make sense in that context. |
||
| Revision | 2020-01-23 17:27 by pmarzullo | ||
| Description | Oracle dialect needs to handle negative years, or BC years. Conversion from timestamp to char for transport to target system: select to_char(to_timestamp('-3546-02-13 12:00:00','SYYYY-MM-DD HH24:MI:SS'), 'SYYYY-MM-DD HH24:MI:SS') from dual; select to_char(to_timestamp('-3546-02-13 12:00:00','SYYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS AD') from dual; select to_char(to_timestamp('3546-02-13 12:00:00 BC','YYYY-MM-DD HH24:MI:SS BC'), 'SYYYY-MM-DD HH24:MI:SS') from dual; select to_char(to_timestamp('3546-02-13 12:00:00 BC','YYYY-MM-DD HH24:MI:SS BC'), 'YYYY-MM-DD HH24:MI:SS AD') from dual; The SYYYY means signed years. The AD indicates BC or AD as appropriate. I would suggest going with SYYYY on parsing and formatting. |
||