CODEWITHSUNDEEP
oracle-database
Jade
Jade

Reputation: 1

Varchar to Timestamp but varchar data is yyyy-mm-dd-hh:mi:ss:ff format

My source is from Oracle and the col1 is varchar2(26) but the value looks like YYYY-MM-DD-hh:mi:ss:ff (Sample rec: 2014-08-15-02.03.34.979946). I have to extract only 6 months records based on COL1. Since there is a hypen between date part and time part - i could not consider as timestamp. Is there any idea how to have this as timestamp to lookup only 6 months data.

Upvotes: 0

Views: 1236

Answers (3)

user5683823
user5683823

Reputation:

If it is possible at all, fix the data first. Storing timestamps in string data type is terrible. How do you know you don't have a time like 25:30:00 in the strings? Or a date like February 30? Besides, you can't really use an index on that column (so queries will be very slow), you will have to write a lot of code whenever referencing that column, etc.

Anyway - to deal with the immediate problem, use TO_TIMESTAMP(), exactly with the format model you show in your post - including the dash between the date part and the time part. Something like this:

select case when to_timestamp('2014-08-15-02.03.34.979946', 'YYYY-MM-DD-HH24:MI:SS.FF')
                 >= systimestamp - interval '6' month
            then 'TRUE' else 'FALSE' end
       as result
from   dual;

RESULT
------
FALSE

EDIT: As Alex Poole points out (correctly as always) in a Comment below this Answer, interval arithmetic won't work correctly in all cases. It is better, than, to use something like

cast ( timestamp (...., format-model) as date ) <= add_months (sysdate, -6).

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191275

You can include the dash in your format model, as @mathguy showed, to convert your string to a timestamp:

select to_timestamp('2014-08-15-02.03.34.979946', 'YYYY-MM-DD-HH24:MI:SS.FF') from dual;

TO_TIMESTAMP('2014-08-15-02.
----------------------------
15-AUG-14 02.03.34.979946000

although unless you explicitly tell it not to be via the FX modifier, Oracle is flexible enough to allow a dash even if the model has a space (see the text below this table in the documentation:

select to_timestamp('2014-08-15-02.03.34.979946', 'YYYY-MM-DD HH24:MI:SS.FF') from dual;

TO_TIMESTAMP('2014-08-15-02.
----------------------------
15-AUG-14 02.03.34.979946000

However, converting all of the values in your col1 column and then comparing them may be a lot of work, and will prevent any index on that string column being used. Given the format, you can convert your date range to string instead, and use string comparison; e.g. to find everything in the six months up to midnight this morning:

select col1 -- or whichever columns you need
from your_table
where col1 >= to_char(cast(add_months(trunc(sysdate), -6) as timestamp), 'YYYY-MM-DD-HH24:MI:SS.FF6')
and col1 < to_char(cast(trunc(sysdate) as timestamp), 'YYYY-MM-DD-HH24:MI:SS.FF6');

or since the time part can be fixed for that example, you can use character literals instead of casting:

select col1 -- or whichever columns you need
from your_table
where col1 >= to_char(add_months(sysdate, -6), 'YYYY-MM-DD"-00:00:00.000000"')
and col1 < to_char(sysdate, 'YYYY-MM-DD"-00:00:00.000000"');

Of course, storing data in the correct native data type would be a much better solution. Any other solution only works at all if your string data actually contains what you think, and the data is all sane (or as sane as it can be in the wrong data type).

Upvotes: 0

Jair Hernandez
Jair Hernandez

Reputation: 494

Maybe something like this will do:

select *
from your_table
where to_date(substr(col1,1,19),'yyyy-mm-dd-HH24.MI.SS') between add_months(sysdate,-6) and sysdate;

Assuming all the data format in col1 is always the same.

Also note that I used HH24 for hour segment, however could be not your case.

Upvotes: 0

Related Questions