Forum Stats

  • 3,767,981 Users
  • 2,252,736 Discussions


Need Help on To_Date Conversion.

User_BQE8U Member Posts: 8 Green Ribbon

Hi Team,

  I am a having a Table "Transaction", it has one Column txn_dt_time, but its data type is Varchar2

  it is containg data like below.



  20-AUG-19 PM +07:00

  21-AUG-19 PM +07:00

  22-AUG-19 PM +07:00



  My Requirement is i want to fetch data less than '21-Aug-2019'. 


  i used the below Queries but it is throwing errors.

  select * from Transaction t where to_date(t.txn_dt_time,'DD-MON-YY') < '21-Aug-2019';

  select * from Transaction t where to_char(to_date(t.txn_dt_time,'DD-MON-YY'),'DD-MON-YY') < '21-Aug-2019';

  select * from Transaction t where TO_DATE(to_char(to_date(t.txn_dt_time,'DD-MON-YY'),'DD-MON-YY'),'DD-MON-YY') < '21-Aug-2019';

  please help me on this Queries.


  • cormaco
    cormaco Member Posts: 1,721 Bronze Crown

    It works like this:

    with transaction(txn_dt_time) as (
        select '20-AUG-19 PM +07:00' from dual union all
        select '21-AUG-19 PM +07:00' from dual union all 
        select '22-AUG-19 PM +07:00' from dual
    select * from transaction
    where to_date(substr(txn_dt_time,1,9),'DD-MON-RR') < date '2019-08-21'
    20-AUG-19 PM +07:00

    Storing dates or timestamps as varchar2 is a bad idea, storing 2 digit years is even worse.

    Also don't compare dates with strings, use to_date or the date literal.

  • StianAtWork
    StianAtWork Member Posts: 40 Blue Ribbon
    edited Nov 17, 2021 2:42PM

    @cormaco you forgot to consider the time zone in the strings. It might not be a problem if he resides in the same time zone as the data but people and servers move some times 😁

    When working with timestamps with time zones this must be considered. Do you want to show all dates older than 21-Aug-2019 at the data origin time zone, or dates older than your local time zone.

    If more than the source in your system contains time zones you should parse and preserve the time zone data whenever possible. If your system only use local time as the truth you should convert the source timestamps to your local time zone as soon as possible. If I'm in doubt I always preserve time zone where I can.

    Building on @cormacos example:

    with transaction(txn_dt_time) as (
      select '20-AUG-19 AM +07:00' from dual union all
      select '20-AUG-19 PM +07:00' from dual union all
      select '21-AUG-19 AM +07:00' from dual union all 
      select '21-AUG-19 PM +07:00' from dual union all
      select '22-AUG-19 AM +07:00' from dual union all
      select '22-AUG-19 PM +07:00' from dual 
    select t.txn_dt_time, 
        To_Timestamp_tz(txn_dt_time,'dd-mon-yy AM TZH:TZM') as txn_dt_time_ts , 
        cast(To_Timestamp_tz(txn_dt_time,'dd-mon-yy AM TZH:TZM') at local as timestamp) as txn_dt_time_local_ts
    from transaction t
    where To_Timestamp_tz(txn_dt_time,'dd-mon-yy AM TZH:TZM') < cast(To_Timestamp_tz('21-Aug-2019','dd-mon-yyyy') at local as timestamp)

    As you can see from my perspective at timezone CET (GMT +01:00)