Hi,
I was wondering how I should convert a timestamp to compare it to
'01-AUG-2006'.

Is any of the following right?

TO_CHAR(TRUNC(MYTIMESTAMP)) < '01-AUG-2006'

TO_DATE(TRUNC(MYTIMESTAMP)) < '01-AUG-2006'

TO_CHAR(MYTIMESTAMP,'DD-MON-YYYY') < '01-AUG-2006'

If you could give a brief explaination of why I am getting a different
result for each of the previous answer, i would really appreciate.

Thank you
Faby

Faby wrote:
If you want to compare timestamps, compare timestamps, not strings.

TRUNC(MYTIMESTAMP) < TO_DATE('01-AUG-2006', fmt_string)

Btw. Do you have TIMESTAMPs, or DATEs? (Though I guess it doesn't make
a difference in this case.)

best,
Martin

Hi Martin,
Thank you for your reply. I need to convert my timestamp to a string as
i'd like to compare it to '01-AUG-2006'. I thought that
TO_CHAR(MYTIMESTAMP,'DD-MON-YYYY') < '01-AUG-2006' would do it, but
someone else from work told me to use
TO_DATE(SUBSTR(TO_CHAR(MYTIMESTAMP), 1, 9), 'DD-MON-RRRR') <
'01-AUG-2006' but i'm sure if he's right or not. Could you explain it,
please?
Thanks
Faby

Faby wrote:
You want to know if your timestamp is before the 1st of August 2006,
right?
Then use:
TRUNC(MYTIMESTAMP) < TO_DATE('01-AUG-2006', fmt_string)

'01-AUG-2006' < '01-JAN-2001' < '02-MAY-0000' - for STRINGS


best,
Martin

Martin T. wrote:
Faby, convert the date string to a timestamp and compare timestamps:

UT1 > select to_timestamp('01-AUG-06')
2 from sys.dual;

TO_TIMESTAMP('01-AUG-06')
---------------------------------------------------------------------------
01-AUG-06 12.00.00.000000000 AM


If your column values have a time component then use >= and <
conditions to ignore the time portion of the column value.

UT1 > l
1 select to_timestamp('01-AUG-06') as BeginTime,
2 to_timestamp(to_date('01-AUG-06') + 1) as EndTime
3* from sys.dual
UT1 > /

BEGINTIME
---------------------------------------------------------------------------
ENDTIME
---------------------------------------------------------------------------
01-AUG-06 12.00.00.000000000 AM
02-AUG-06 12.00.00 AM

HTH -- Mark D Powell --

Hi all,
Thank you very much for your answers. I have now a better understanding
of the timestamp data type.:-)
I haven't been able to convince anyone from work to use any of your
ideas, though i'm sure that my next scripts will hold one of your
version. It really makes more sense to me.
Thanks again
Faby