How to convert string to date format when string has only two digits for a year (Oracle, Netezza)

0
votes

I have this issue databases: on Oracle and Netezza.

ISSUE: I have a string: 16101211213. This string means: YYMMDDHH24MMSS. I need to convert it to date format YYYY-MM-DD HH24:MM:SS. So on the way I need to add the two digits (in front of the string). I know that dates are for XXI century. So I need to 20 at the begining.So in result I should get 2016-10-12 21:12:13

Can anybody help me with it? I have tried many options (mainly on Netezza) but could not figure it out.

Thanks a lot in advance!

2
databasestringoracledatenetezza
Please read stackoverflow.com/help/how-to-ask and share your attempts - Lord_Curdin
Sorry, But I don't know what's wrong with this question.... I searched the topics related and couldnot find the solution.... - miki kiki

2 Answers

1
votes

RR might be your savior.

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> with test as (select '16101211213' datum from dual)
  2  select to_date(datum, 'rrmmddhh24miss') result
  3  from test;

RESULT
-------------------
12.10.2016 11:21:03

SQL>

Oh, and - be careful! You used wrong format mask for minutes; it is "MI", not "MM" (which is a month).

[EDIT: displaying result using a different format mask]

SQL> with test as (select '16101211213' datum from dual)
  2  select
  3    to_date(datum, 'rrmmddhh24miss') result_1,
  4    to_char(to_date(datum, 'rrmmddhh24miss'), 'mm/dd/yyyy hh:mi:ss am') result_2
  5  from test;

RESULT_1            RESULT_2
------------------- ----------------------
12.10.2016 11:21:03 10/12/2016 11:21:03 AM

SQL>
1
votes

You can either reply on Oracle inferring the year by converting with the YY or RR format model elements, or concatenate the century value and use YYYY.

If you are really sure that the dates are all in the 21st century then using concatenation and YYYY:

to_date('20' || tistamp, 'yyyymmddhh24miss')

will behave the same as using YY (which uses the current date to decide the century):

to_date(tistamp, 'yymmddhh24miss')

and if all the years are below 50 then RR (which uses the current date's century or the last century depending on the supplied 2-digit year) will also get the same result:

to_date(tistamp, 'rrmmddhh24miss')

But if any of the values are 50 or above RR and YY/YYYY behave differently. As these seem to be event timestamps it's unlikely they will be in the future, but the difference may still matter one day. (But then, eventually, assuming 21st century might not be valid either...)

Quick demo of the difference, using your sample value and a couple of others, supplied via a CTE:

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

with your_table(tistamp) as (
  select '16101211213' from dual
  union all select '491231235959' from dual
  union all select '500101000000' from dual
)
select to_date('20' || tistamp, 'yyyymmddhh24miss') as yyyy,
  to_date(tistamp, 'yymmddhh24miss') as yy,
  to_date(tistamp, 'rrmmddhh24miss') as rr
from your_table;

YYYY                YY                  RR                 
------------------- ------------------- -------------------
2016-10-12 11:21:03 2016-10-12 11:21:03 2016-10-12 11:21:03
2049-12-31 23:59:59 2049-12-31 23:59:59 2049-12-31 23:59:59
2050-01-01 00:00:00 2050-01-01 00:00:00 1950-01-01 00:00:00

All of these would also work with to_timestamp() of course; as you don't have fractional seconds or time zone info using dates should be fine as long as your client knows that Oracle dates have a time component.