Selecting Strings and Converting to Dates with Oracle

[Saint Nick]

select time_stamp
from tidalard;

Returns a string in the following format. This is not a date type.

1998-09-15-17.31.27.001000

I modified it so it takes only the date using the below query.

select replace(substr(time_stamp, 0, 10), '-', '/') as ts_date
from tidalard;

Unsurprisingly returns

1998/09/15

Now, I've tried various combinations of to_date() and to_char() to convert this to a date. My desired output is:

09/15/1998

I'm not sure what else to do, if there is a way...

 

[KLin]

http://www.club-oracle.com/forums/convert-oracle-timestamp-to-date-t692/

 

[MrChad]

select
    to_char(
        to_date(
            replace(substr(time_stamp, 0, 10), '-', '/'),
            'YYYY/MM/DD'),
        'MM/DD/YYYY')
from tidalard;

 

[Saint Nick]

Huh, I swear I tried that and it didn't work, but it did this time...thanks to both of you!

 

[Seero]

SELECT TO_CHAR (TO_TIMESTAMP (time_stamp, 'YYYY-MM-DD-HH24.MI.SS.FF9'), 'MM/DD/YYYY') FROM tidalard;

That way you don't lose the precision when you want to ditch the to_char.

 

[ForumMaster]

or... you could just do this:

select sysdate from dual;

this will return a date type without having to use to_char and all that.
systimestamp obviously returns a timestamp which is more accurate, but not a date type.

good luck.

the output will depend on your NLS_DATE_FORMAT which can be viewed by issuing the following query:
select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

if you want to change it, you can do like this:
alter session set nls_date_format = 'MM/DD/YYYY HH24:MI:SS';

the sysdate will then return the date in the desired output.