2

Bit stuck here. Say I run the following SQL query:

select to_char(sysdate, 'DD/MON/YY') || ' 15:32:00' from dual;

The result gives me this:

08/NOV/12 15:32:00

Now I want to take that result and use it in a to_date function (long story as to why, but I'll go into detail in a sec):

select to_date((to_char(sysdate, 'DD/MON/YY') || ' 15:32:00'), 'DD/MON/YY HH24:MI:SS') from dual;

I was expecting the result to be the same as the previous one, except it isn't. Instead it gives me this:

08/NOV/12

Question is this: Is there a way I can use a concatenation of a to_char(sysdate, 'DD/MON/YY') and any combo of HH24:MI:SS in a to_date?

Additional detail that may or may not be of any additional use: Reason I need to do this is because I was provided a query that has a table with an SLA time. The format is just a time in HH24:MI, however it's not a DATETIME type (I suspect it's a VARCHAR, but can't tell for sure as it's likely a view or function of some sort that I cannot dissect. Partially due to me not knowing how to and partially to me not having the necessary access due to my DB user.

EDIT: On reading the above again I realized I left out some additional detail (though this does not relate to my question at all I think): I want to take today's date (DD/MON/YY), combine that with the SLA time of HH:MI, combine that with ':00' for :SS, convert the whole thing to a date, then do a comparison between the resultant abomination and the finish time for each row returned. If the finish time is bigger/ newer/ later than my combination-and-conversion-deluxe, then return something specific, in not, return something like "All OK". Not sure if that makes sense or not, but in summary, I want to get something that I can use to compare to another DATETIME type, so I think I need to somehow get the SLA time converted to a DATETIME type as well.

Any help on this will be greatly appreciated.

kooper
  • 97
  • 2
  • 9
  • 1
    What client are you using to run this? It should work, [demo](http://www.sqlfiddle.com/#!4/5a096/1), – Ben Nov 08 '12 at 11:32
  • Hi Ben, using Oracle SQL Developer to test it. I was pretty sure it should work just fine, but for some bizarre reason it seems to just throw the bit after the to_char(sysdate, 'DD/MON/YY') away. EDIT: SQL Developer v. 2.1 – kooper Nov 08 '12 at 11:42
  • sql developer NLS settings are under Tools>Preferences>Database>NLS Parameters. see answer below too – DazzaL Nov 08 '12 at 11:44

2 Answers2

2

the output you see is converted to a char format. this is dictated by the NLS_DATE_FORMAT setting. internally, the date will still have the time format associated. so do this to see it:

SQL> select sysdate from dual;

SYSDATE
---------
08-NOV-12

SQL> alter session set nls_date_format='dd/MON/yy hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
------------------
08/NOV/12 11:41:46
DazzaL
  • 21,638
  • 3
  • 49
  • 57
  • DazzaL, you brilliant thing you! :) I didn't run the alter session, but based on that I had a dig around SQL Developer's settings and ended up in Tools > Preferences > Database > NLS > Date format and change it from DD/MON/RR to DD/MON/RR HH24:MI:SS. Reran my query after that and lo and behold, it's working like a charm now. Thanks for the quick help, really appreciate it. – kooper Nov 08 '12 at 11:46
2

You're default date format is 'DD/MM/YY' If you want to display the time component you'll have to use the right date format:

select to_char(to_date((to_char(sysdate, 'DD/MON/YY') || ' 15:32:00'), 'DD/MON/YY HH24:MI:SS'),'DD/MM/YY HH24:MI:SS') from dual
Rob van Laarhoven
  • 8,737
  • 2
  • 31
  • 49
  • Thanks for that, didn't occur to me to throw in another to_char function. It makes a lot more sense to me now after your and DazzaL's answers, seems I managed to convert it to a DATETIME type just fine, but it just wasn't showing what I wanted, even though it was there. – kooper Nov 08 '12 at 11:50