-1

I have a fairly simple problem that I cannot resolve... I need to display date without time (getting rid of time by still displaying 00:00:00 won't do in my case), so solutions like 

select trunc(sysdate) from dual;

select TO_DATE('20160517','YYYYMMDD') from dual;

do not really solve my problem.

What is more, my Oracle does not support covert() or dateadd(),datediff(),getdate() functions. Any help appreciated!

Edit: the purpose of this is to concatenate the existing date with some hard code time.

Janna Sherazi
  • 167
  • 1
  • 1
  • 15

2 Answers2

2

If you want display some DATE field without time just use TO_CHAR function. For exmaple:

SELECT to_char(sysdate, 'DD.MM.YYYY')
  FROM dual
hinotf
  • 1,138
  • 1
  • 12
  • 22
0

Thanks @hinotf! I've figured out I could do something like this. It's not beautiful but at least Oracle does not complain about the wrong date format. 

select to_date(to_char(sysdate , 'dd.mm.yyyy')||' 11:01:00') from dual;
Janna Sherazi
  • 167
  • 1
  • 1
  • 15
  • 1
    What is it you're trying to do? Create a date at a specific time? I'd typically do something like: `select trunc(sysdate) + 11/24 + 1/(24*60) from dual;` rather than converting to a string and then back to a date again. – Boneist May 17 '16 at 14:05
  • 2
    You're relying on your NLS settings when converting back to a date. You should at least do `to_date(to_char(sysdate , 'dd.mm.yyyy')||' 11:01:00', 'dd.mm.yyyy hh24:mi:ss')`. But there are other ways to manipulate the time, depending on where the `11:01:00` part is actually coming from. – Alex Poole May 17 '16 at 14:05
  • Hi @AlexPoole, thanks for your answer. Could you be so kind and explain what is the risk of not specifying 'dd.mm.yyyy hh24:mi:ss' ? Thank you! – Janna Sherazi May 17 '16 at 14:09
  • 3
    The risk is you run it in a session which has a different NLS_DATE_FORMAT and the `to_date()` fails because the string you've constructed doesn't match that format. (Or potentially works but gets the wrong value, if MM and DD and swapped, though that fails too with high day numbers). Avoid relying on NLS settings - don't make assumptions about the session that will run the code. You can get odd side effects too. – Alex Poole May 17 '16 at 14:11