Re: Trouble selecting dates

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 7 Apr 2002 15:34:23 -0700
Message-ID: <a8qhhf012k4@drn.newsguy.com>

In article <lo2s8.2010$p4.308087_at_news-binary.blueyonder.co.uk>, "Bill says...
>
>The request_date in the absence_history table may have a milli-second
>component and unless this is 0 then your SELECT statement will not return
>any rows. I think that you will have to try something like
>
>select request_date
>from new_absence_history
>where trunc( request_date, 'YYYY-MM-DD HH:MI:SS') = to_date('2002-03-13
>03:19:39','YYYY-MM-DD HH:MI:SS');
>
>I would also suggest that if you are only interested in the date (and not
>the time) component, then maybe your date format mask should be
>'YYYY-MM-DD'.
>
>Regards,
>
>Bill

(didn't see original post, hence follow up on this one)

Probably more likely its this

the times are 3pm, not 3am in the table. to_date('2002-03-13 03:19:39','YYYY-MM-DD HH:MI:SS')

is 3:19:39 AM, not PM

Consider:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t ( x date );

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( to_date( '01-jan-2001 03:19:39', 'dd-mon-yyyy hh:mi:ss' ) );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( to_date( '01-jan-2001 15:19:39', 'dd-mon-yyyy hh24:mi:ss' ) );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> ALTER SESSION SET NLS_DATE_FORMAT =

'YYYY-MM-DD HH:MI:SS'; Session altered.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select x from t;

2001-01-01 03:19:39
2001-01-01 03:19:39

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select x from t where x=to_date('2001-01-01 03:19:39','YYYY-MM-DD HH:MI:SS'); X

2001-01-01 03:19:39

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>

Looks like both should come back, but only the 3am one does (and rightly so)

Use Military time -- you want 15 hundred hours, not 3 hundred hours....

>
>
>"Nigel Reed" <nelgin_at_nortelnutworks.com> wrote in message
>news:34kraugstdroe4r9qpmp78k041sobrrpkr_at_4ax.com...
>> I've been dumped a project and not really an Oracle type, so I hope
>> someone can help me with this issue.
>>
>> SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH:MI:SS';
>>
>> Session altered.
>>
>> SQL> select request_date from new_absencehistory where
>> absence_type='PH';
>>
>> REQUEST_DATE
>> -------------------
>> 2002-03-13 03:12:55
>> 2002-03-13 03:19:39
>>
>> SQL> select request_date from new_absencehistory where
>> request_date=to_date('2002-03-13 03:19:39','YYYY-MM-DD HH:MI:SS');
>>
>> no rows selected
>>
>> Now my question is, why can't I select on this date?
>>
>> Any advice would be appreciated cos I'm stuck.
>>
>> Regards
>> Nigel
>>
>> ** When replying via email please change the nut to net **
>> --
>> Nigel Reed
>> DES Engineering & Infrastructure
>> Nortel Networks, 2201 Lakeside Blvd, Richardson, TX 75082
>> ESN 444 8859 External 972 684 8859 Mailstop: 992/02/A30
>> We will never give Bill Clinton the opportunity to be the President of the
>United States.
>> - Vice President Dan Quayle (9/19/92)
>> * Any opinion expressed in this message is mine and not that of Nortel
>Networks *
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Sun Apr 07 2002 - 17:34:23 CDT