CODEWITHSUNDEEP
sqloracle-databasetimestamp
delroekid
delroekid

Reputation: 88

Oracle to_timestamp

quick question on Oracle To_timestamp.

I have a two fields in a table that are both varchars

1 field contains the YYYYMMDD formatted value the 2nd field contains the hh24mmss formatted value

Im trying to convert these two fields into a timestamp field,

expected output is DD/MM/YYYY HH24:mm:ss like '7/23/2015 12:53:04'

however, it gaves me weird result... like '7/15/0023 12:53:04'

seems year becomes day and day becomes year..

---------- heres my sql ----------------

select 


  to_date(to_char(to_date('20150723','yyyymmdd'),'yyyymmdd'),'yyyymmdd') dt,
  to_char(to_date(SUBSTR('005304000',1,6), 'hh24miss'), 'hh24:mi:ss') tm,

 TO_TIMESTAMP(
 to_date(to_char(to_date('20150723','yyyymmdd'),'yyyymmdd'),'yyyymmdd')  ||  
 to_char(to_date(SUBSTR('005304000',1,6), 'hh24miss'), 'hh24:mi:ss'), 'yyyy/mm/dd HH24:MI:SS'  ) dttm

  from dual

Upvotes: 1

Views: 704

Answers (1)

user330315
user330315

Reputation:

You have one layer to much in your conversion chain. And it will be much easier if you concatenate the two columns before converting them:

to_date(date_column||time_column, 'YYYYMMDDHH24MISS')

will return a date and a time (an Oracle DATE includes a time part).

Now that you have a proper date value, you can then format that as you want:

to_char(to_date(date_column||time_column, 'YYYYMMDDHH24MISS'), 'yyyy/mm/dd HH24:MI:SS')

As your "time" column doesn't contain milliseconds there is no real benefit in using to_timestamp.

But you should really fix your table, and store that information in a single DATE (or TIMESTAMP) column. Storing dates in varchar columns is always a very bad choice.

Upvotes: 4

Related Questions