date checking in a single select statement 1

[joereena]

Hi,

I want to see whether a given date is equal to, greater than or less than sysdate. How can I do it using a single select statement.

Please Help.
Thanx in advance.

 

[Guest_imported]

I will try:
select to_date(given_date) from your_table
where to_date(given_date,'mm/dd/yyyy') < to_char(sysdate,'mm/dd/yyyy')
or to_date(given_date,'mm/dd/yyyy') = to_char(sysdate,'mm/dd/yyyy')
or to_date(given_date,'mm/dd/yyyy') > to_char(sysdate,'mm/dd/yyyy');
or you can test them individually by testing for:
to_date(given_date,'mm/dd/yyyy') < to_char(sysdate,'mm/dd/yyyy') first

and to_date(given_date,'mm/dd/yyyy') = to_char(sysdate,'mm/dd/yyyy') second
or to_date(given_date,'mm/dd/yyyy') > to_char(sysdate,'mm/dd/yyyy') third
or any combination.

 

[cwong5]

joereena,

How do you want to show it? In an message box as the date is being saved? or in a different field?

Chester

 

[sem]

If your field is of DATE datatype you may

select decode(sign(your_date_field - sysdate), 1, 'MORE'
, -1, 'LESS', 0, 'EQUALS', 'UNDEFINED') from dual

If it's of some character datatype you may use the same making conversions with to_date().

 

[joereena]

Thanks a lot sem, it works