0

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)

Nicholas
  • 783
  • 2
  • 7
  • 25
  • You can't have a date column that only holds a time. You're getting `01-JUL-2013` because [as the docs say](http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements003.htm#SQLRF51062): "If you specify a date value without a date, then the default date is the first day of the current month". – Alex Poole Jul 17 '13 at 09:47
  • Argh i see, thanks Alex. So there is no way to show only the time in a Date type column - if i understood correctly? – Nicholas Jul 17 '13 at 09:50
  • You can't store only the time; you can show whatever you want when you query. If you only wanted to store a time you could look at [this question](http://stackoverflow.com/q/12215281/266304), but I'm unclear why you're storing an arbitrary date when the value is null - why not just store null rather than a magic date? Are you really not talking about storing at all, this is only about display values that are already stored? – Alex Poole Jul 17 '13 at 09:53
  • Yeah this is about displaying the data that is stored. So, if the data is null then i should display a date, however if there is already a date i would only like to show a time. My question is, is possible to do in only 1 column? – Nicholas Jul 17 '13 at 09:56
  • Do you need to work with this values in other query/procedure, or you nedd just an output? Because, if you need just an output, you can just convert all values to char however you want. – Mikhail Jul 17 '13 at 10:01

2 Answers2

1

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
0

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

user2572367
  • 45
  • 7