search

orafce / orafce

The "orafce" project implements in Postgres some of the functions from the Oracle database that are missing (or behaving differently).Those functions were verified on Oracle 10g, and the module is useful for production work.
Other
490 stars 156 forks source link

orafce's to_date is different from Oracle's to_date #206

Closed aaaspirin closed 1 year ago

aaaspirin commented 1 year ago

Postgres: select to_date('23-05-2020', 'dd-mm-yyyy') from dual; to_date

2020-05-23 00:00:00 (1 row)

Oracle: select to_date('23-05-2020', 'dd-mm-yyyy') from dual; TO_DATE('

23-MAY-20

okbob commented 1 year ago

po 9. 1. 2023 v 10:50 odesílatel aaaspirin @.***> napsal:

Postgres: select to_date('23-05-2020', 'dd-mm-yyyy') from dual; to_date

2020-05-23 00:00:00 (1 row) Oracle: select to_date('23-05-2020', 'dd-mm-yyyy') from dual; TO_DATE('

23-MAY-20

It is correct - PostgreSQL uses different default >>output<< format. If you want to change it, you should to use to_char function.

(2023-01-09 11:00:22) postgres=# select oracle.to_char(oracle.to_date('23-05-2020', 'dd-mm-yyyy')) from oracle.dual; ┌──────────────────────────┐ │ to_char │ ╞══════════════════════════╡ │ Sat May 23 00:00:00 2020 │ └──────────────────────────┘ (1 row)

(2023-01-09 11:05:23) postgres=# select oracle.to_char(oracle.to_date('23-05-2020', 'dd-mm-yyyy')) from oracle.dual; ┌─────────────────────┐ │ to_char │ ╞═════════════════════╡ │ 2020-05-23 00:00:00 │ └─────────────────────┘ (1 row)

(2023-01-09 11:05:25) postgres=# set orafce.nls_date_format='DD-Mon-YY'; SET (2023-01-09 11:05:29) postgres=# select oracle.to_char(oracle.to_date('23-05-2020', 'dd-mm-yyyy')) from oracle.dual; ┌───────────┐ │ to_char │ ╞═══════════╡ │ 23-May-20 │ └───────────┘ (1 row)

— Reply to this email directly, view it on GitHub https://github.com/orafce/orafce/issues/206, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEFO47H3SEZWDVPWDXKIATWRPNPBANCNFSM6AAAAAATVHAWFY . You are receiving this because you are subscribed to this thread.Message ID: @.***>

aaaspirin commented 1 year ago

Thanks，and why orafce's todate keep the hours, minutes and seconds by default instead of keeping consistent with Oracle? Oracle through nls date_ format control the date display format, the default value is DD-MON-RR, but orafce's to_date can't.

okbob commented 1 year ago

po 9. 1. 2023 v 11:27 odesílatel aaaspirin @.***> napsal:

Thanks，and why orafce's todate keep the hours, minutes and seconds by default instead of keeping consistent with Oracle? Oracle through nls date_ Format Control the date display format, the default value is DD-MON-RR, but orafce's to_date can't.

orafce doesn't implement its own datatypes - it +/- maps Oracle's functionality to PostgreSQL's functionality. PostgreSQL types are much more strict against Oracle's types. PostgreSQL date is only date, but Oracle's date is date, but can be timestamp too. So the most near enough general type to Oracle's date is PostgreSQL's timestamp(0). Orafce doesn't try to support full compatibility with Oracle. It just tries to reduce necessary work when some applications are ported from Oracle. Full compatibility is not possible without deeper changes in PostgreSQL, and it is not possible from extension.

—

Reply to this email directly, view it on GitHub https://github.com/orafce/orafce/issues/206#issuecomment-1375398763, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEFO46QSN6UWOB7RKIQIJLWRPRYTANCNFSM6AAAAAATVHAWFY . You are receiving this because you commented.Message ID: @.***>