Re: Oracle Timestamp

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 10 Aug 2006 09:41:36 -0700
Message-ID: <1155228096.114700.88080@p79g2000cwp.googlegroups.com>

Martin T. wrote:
> Faby wrote:
> > 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?
> >
> Why would you want to compare the date as a string?
> 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)
>
> > TO_CHAR(MYTIMESTAMP,'DD-MON-YYYY') < '01-AUG-2006'
> >
> This will compare strings, and is most likely not what you want!
> '01-AUG-2006' < '01-JAN-2001' < '02-MAY-0000' - for STRINGS
>
> > 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,
> >
> This does make even less sense to me ...
>
> best,
> Martin

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 -- Received on Thu Aug 10 2006 - 11:41:36 CDT