How to select records based on Date 1

[fiat2]

I'm trying to select records that have workdates greater than 4/1/2009.

workdate is of type Date is stamped with sysdate when inserted so it contains Date & Time

How can I achieve this?

SELECT * FROM  tb_my_table 
WHERE to_char(workdate, 'MM/DD/YYYY') > ('4/1/2009')

 

[SantaMufasa]

Fiat,

Try this:

SELECT * FROM  tb_my_table 
WHERE workdate > to_date('04/01/2009','mm/dd/yyyy');

Let us know of the outcome.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[fiat2]

Well, it must be Christmas in April because Santa just brought me a gift. It worked and provided me the results I needed.

Thanks for taking the time.

Enjoy,

 

[ChrisHunt]

Bear in mind that that query will include rows where the workdate is 4/1/2009, as [tt]to_date('04/01/2009','mm/dd/yyyy')[/tt] will evaluate to midnight on 1st April and your workdate includes a time component. So any row with a workdate of, say, 1pm on 4/1/2009 will still be included in your report.

If this is a problem for you, there are several ways around it, here's one:

SELECT * FROM  tb_my_table 
WHERE [red]TRUNC([/red]workdate[red])[/red] > to_date('04/01/2009','mm/dd/yyyy');

here's another

SELECT * FROM  tb_my_table 
WHERE workdate >[red]=[/red] to_date('04/0[red]2[/red]/2009','mm/dd/yyyy');

The second is probably better, as it will continue to use any index you might have on workdate.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd