当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Questions on To_Date function

问题描述:

I have two questions,

1.

Why can't I get HH24:MI:SS when using To_date function?

select To_date(fn_adjusted_date(SUBMIT_DATE),'DD-MON-YY-HH24:MI:SS')

from HPD_Help_Desk;

16-NOV-08

select To_char(fn_adjusted_date(submit_date),'DD-MON-YY-HH24:MI:SS')

from HPD_Help_Desk;

16-NOV-08-06:01:10

2.

Why am I getting an error when using:

To_date(fn_adjusted_date(SUBMIT_DATE),'DD-MON-YY-HH24:MI:SS')

but changing it works fine when I change it to:

To_date(fn_adjusted_date(SUBMIT_DATE),'DD-MM-YY-HH24:MI:SS')

To demonstrate:

select sysdate from dual;

03-MAR-15

alter session set nls_date_format = 'dd-mm-yyyy hh24:mi:ss';

select sysdate from dual;

03-03-2015 11:29:22

select To_date(fn_adjusted_date(SUBMIT_DATE),'DD-MON-YY-HH24:MI:SS')

from HPD_Help_Desk;

ORA-01843: not a valid month 01843. 00000 - "not a valid month"

select To_char(fn_adjusted_date(submit_date),'DD-MON-YY-HH24:MI:SS')

from HPD_Help_Desk;

16-NOV-08-06:01:10

网友答案:

1. Because to_date() gives you a date object, and you're leaving it up to your client to decide how to display that as a string; it's likely to be using your NLS_DATE_FORMAT settings.

Since your fn_adjusted_date() function returns a date not a string, do not then call to_date() on that; you're doing an implicit conversion to a string and then back to a date, both using NLS_DATE_FORMAT, and from how your first query is displayed - as DD-MON-YY? - that is losing the time portion anyway. So you're really doing:

select to_date(to_char(fn_adjusted_date(SUBMIT_DATE), 'DD-MON-YY'),
  'DD-MON-YY-HH24:MI:SS') from HPD_Help_Desk;

2. Because MON is the abbreviated month name in your date language. This follows on from the first point; now in the first of those you're doing an implicit to_char() of your value using the new NLS_DATE_FORMAT, which specifies the month number with MM, but then you try to convert that back to a date with MON. So this time you're really doing:

select to_date(to_char(fn_adjusted_date(SUBMIT_DATE), 'dd-mm-yyyy hh24:mi:ss'),
  'DD-MON-YY-HH24:MI:SS') from HPD_Help_Desk;

And 11 is not a valid month name. Oracle is quite flexible with date formats when it can be; it can interpret 'NOV' using the MM model even though that doesn't make sense really since it isn't a number, but the meaning is pretty obvious; from your example in a comment:

select to_date('16-Nov-2008', 'DD-MM-YY') from dual;

TO_DATE('16-NOV-2008','DD-MM-YY')
---------------------------------
16-NOV-2008 00:00:00              

It doesn't work the other way though; it can't interpret 11 using MON. That flexibility can appear inconsistent, and it sometimes seems to be too forgiving.

In the second query you're doing an explicit to_char() with a format model specified, which is the correct way to display a date as a string.

The underlying messages are the same for both: don't call to_date() when you already have a date object, don't ever rely on implicit conversion, and don't convert a date to a string while you're still processing it - only if you want it as a string in a specific format in your final result set.

分享给朋友:
您可能感兴趣的文章:
随机阅读: