CODEWITHSUNDEEP
sqloracle-databasecase
Nicholas
Nicholas

Reputation: 793

Is it possible to have 2 formats of Dates inside 1 column?

I would like to know if it is possible to have 2 formats of dates inside one column.

The problem is that I need to have a default value if the table is NULL which is a date, and if the table is NOT null then i would need just a timestamp.

I've made a CASE that should do this, but it won't work.

CASE WHEN s.time IS null 
THEN 
  TO_DATE('01-JAN-1901 01:00:00', 'dd-MON-yyyy HH24:MI:SS') 
ELSE
  TO_DATE(s.time, 'HH24:MI:SS') 
END AS time

That statement gives me "ORA-01858: a non-numeric character was found where a numeric was expected."

If i try something like this:

CASE WHEN s.time IS null 
THEN 
  TO_DATE('01-JAN-1901 01:00:00', 'dd-MON-yyyy HH24:MI:SS') 
ELSE 
  TO_DATE(TO_CHAR(s.time, 'HH24:MI:SS'), 'HH24:MI:SS') 
END AS time

It will work, however it will add a date in front of the time - and it will look like this: (i don't know where it is getting that date from 01-JUL-2013)

 TIME
-------
01-JAN-1901 01:00:00
01-JUL-2013 15:35:00

instead of this: 

 TIME
-------
01-JAN-1901 01:00:00
15:35:00

(this is what i want)

Upvotes: 0

Views: 76

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

Since it's for display only, you need to use TO_CHAR() instead of TO_DATE() in your ELSE, but both branches have to produce a string:

CASE WHEN s.time IS null 
THEN 
  TO_CHAR(TO_DATE('01-JAN-1901 01:00:00', 'dd-MON-yyyy HH24:MI:SS')) 
ELSE
  TO_CHAR(s.time, 'HH24:MI:SS') 
END AS time

The TO_CHAR(TO_DATE()) is redundant unless you want to rely on your NLS_DATE_FORMAT, so this is simpler:

CASE WHEN s.time IS null 
THEN 
  '01-JAN-1901 01:00:00'
ELSE
  TO_CHAR(s.time, 'HH24:MI:SS') 
END AS time

SQL Fiddle of both approaches.

You get the ORA-01858 from your initial version because you're passing a DATE into TO_DATE(). That expects a string argument and this is causing an implicit conversion, so you're really doing:

TO_DATE(TO_CHAR(s.time), 'HH24:MI:SS')

which based on what your NLS_DATE_FORMATsetting seems to be is:

TO_DATE(TO_CHAR(s.time, 'DD-MON-YYYY HH24:MI:SS'), 'HH24:MI:SS')

so you'd end up trying to do something like:

TO_DATE('16-JUL-2013 15:35:00', 'HH24:MI:SS')

and you get the error because it's then trying to interpret JUL (or whatever month your real data has) as a numeric value for the minutes value, for the MI part of the format mask you supplied.

Upvotes: 1

user2572367
user2572367

Reputation: 217

You can use substring and remove the date

CASE WHEN s.time IS null THEN
TO_DATE('01-JAN-1901 01:00:00', 'dd-MON-yyyy HH24:MI:SS')
ELSE SUBSTRING() END AS time

Upvotes: 0

Related Questions