29 November 2006

Date Time Related Data Types And Functions In Oracle

Developers always use time related data types in their applications to audit information. Oracle give additional functionality supplied functions and data types. One of my previous posts i mentioned how to use interval data types. Now i want to give some examples how to use some of them.
First it is possible to convert interval data from string values:
SQL> SELECT to_yminterval('02-10') FROM dual;

TO_YMINTERVAL('02-10')
----------------------
+000000002-10

SQL> SELECT to_dsinterval('02 01:00:30') FROM dual;

TO_DSINTERVAL('0201:00:30')
---------------------------
+000000002 01:00:30


Second, i think more powerful, convert datetime strings from external programing languages such as C# or Java, Oracle timestamps and manipulate easiliy with them. Suppose you want to convert format of a datetime string. ( '23:12:54.899 +02:00 Thu Nov 29 2001' to '011129' )With oracle's supplied functions you are not need to make conversions such find-replace. Look at example below:

SQL> SELECT tz_offset('Asia/Istanbul') FROM dual;

TZ_OFFSET('ASIA/ISTANBUL')
--------------------------
+02:00

SQL> SELECT to_timestamp_tz('23:12:54.899 +02:00 Thu Nov 29 2001','HH24:MI:SS.FF3 TZH:TZM DY Mon DD YYYY') FROM dual;

TO_TIMESTAMP_TZ('23:12:54.899+'
-------------------------------------------------
29-NOV-01 11.12.54.899000000 PM +02:00

SQL> SELECT to_char( to_timestamp_tz('23:12:54.899 +02:00 Thu Nov 29 2001','HH24:MI:SS.FF3 TZH:TZM DY Mon DD YYYY'), 'YYMMDD') FROM dual;

TO_CHAR(TO_TIMESTAMP_TZ('23:12'
------------------------------
011129

No comments: