CODEWITHSUNDEEP
oracle-database
user6767022
user6767022

Reputation: 105

Issue with converting date to String

I am trying to update a query, to reformat the way a datetime field is being output, so that it can used in a different system. I feel like I have looked into and tried many potential solutions, but they all still result in an error when trying to execute the query.

My data looks has the format 'YYYYMMDD HH24:MI:SS'. I want to reformat the field to 'YYYY-MM-DD HH24:MI:SS'.

examples of data:

20171122 11:16:42
20171121 15:28:48
20171117 12:49:44
20171121 16:25:30
20171121 15:23:02
20171122 10:10:27
20180214 17:49:41
20180223 13:51:54
20180612 17:45:52
20180628 14:25:41
20180214 13:31:19
20190328 14:32:39
20190319 11:43:25
20190321 13:11:03
20190319 10:24:29
20190321 13:11:25
20190319 10:21:11

I've tried using to_char(DateTime_Col,'YYYY-MM-DD HH24:MI:SS') in my statement. when doing so, I get the following error.

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

I've also tried using to_date(to_char(DateTime_Col),'YYYY-MM-DD HH24:MI:SS') or just to_date(DateTime_Col,'YYYY-MM-DD HH24:MI:SS') and from both of these, I get the following.

ORA-00932: inconsistent datatypes: expected CHAR got DATE
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Error at Line: 4 Column: 22

I feel like this should be simple, but a solution is escaping me. I feel like I have looked at lots of different information and tried more than just what I have above, but I can't come up with a working solution.

Thanks for any assistance.

Upvotes: 2

Views: 212

Answers (1)

user5683823
user5683823

Reputation:

It seems that your so-called "dates" are in fact strings (perhaps in varchar2 data type).

If so, first you must convert them to dates with to_date and with their current format model, and then convert the resulting dates back to string with the desired format model. Something like this:

select to_char( to_date(DateTime_Col, 'yyyymmdd hh24:mi:ss')
              , 'yyyy-mm-dd hh24:mi:ss' )
........

In your example, it would also be quite easy to add dashes to the existing string (since that's the only change), but the solution I just showed you is much more general and has another advantage: if one of the input strings is in fact not a valid date, the query will tell you so, by throwing an error. If you just use string manipulations, you may miss those instances.

Upvotes: 3

Related Questions