Re: help! DATE problem

In article <7c16h9$hl7$1_at_nnrp1.dejanews.com>,

        kal121_at_yahoo.com writes:
> I have a query that goes like this:
>
> for example:
> SELECT creation_date FROM my_table WHERE creation_date <= '01-JAN-99';
>
> Problem is, I've got data in my_table that looks like this:
>
> 01-JAN-99 09:34:30
> 01-JAN-99 11:10:24
> 01-JAN-99 11:15:43
>
> So effectively, when I run this query, it WILL NOT select the above dates
> since those dates are not before 01-JAN-99 00:00:00 - which is what is being
> specified in the above query.
>
> I've tried to manipulate the date with the TO_DATE function:
> for example:
> select * from my_table where creation_date <= to_date('09-03-99 01:52:30',
> 'DD-MM-YY HR24:MI:SS') This does not work.
>
> Does anybody know how to make this work?

What you have tried _does_ work, but '01:52' in the format 'HH24:MI' means 1:52 AM.

What could work is something like this:

select * from mytable
where trunc(creation_date) = to_date('09-03-1999','DD-MM-YYYY');

Explore the TRUNC function, it can have a second argument which specifies the unit to which the date is to be truncated. Try this:

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

select trunc(sysdate) from dual;
select trunc(sysdate,'MI') from dual;
select trunc(sysdate,'HH') from dual;
select trunc(sysdate,'DD') from dual;
select trunc(sysdate,'MM') from dual;
select trunc(sysdate,'YY') from dual;

Remco
--
rd31-144: 1:05am up 3 days, 23:57, 7 users, load average: 1.00, 1.03, 1.03 Received on Mon Mar 08 1999 - 18:17:26 CST