Forum Stats

  • 3,767,981 Users
  • 2,252,736 Discussions
  • 7,874,399 Comments

Discussions

Need Help on To_Date Conversion.

User_BQE8U
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.

  txn_dt_time

  -----------

  20-AUG-19 01.45.51.049098 PM +07:00

  21-AUG-19 01.45.51.064047 PM +07:00

  22-AUG-19 01.45.51.073921 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.

Answers

  • cormaco
    cormaco Member Posts: 1,721 Bronze Crown

    It works like this:

    with transaction(txn_dt_time) as (
        select '20-AUG-19 01.45.51.049098 PM +07:00' from dual union all
        select '21-AUG-19 01.45.51.064047 PM +07:00' from dual union all 
        select '22-AUG-19 01.45.51.073921 PM +07:00' from dual
    )
    select * from transaction
    where to_date(substr(txn_dt_time,1,9),'DD-MON-RR') < date '2019-08-21'
    
    TXN_DT_TIME                        
    -----------------------------------
    20-AUG-19 01.45.51.049098 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.

    Niall
  • 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 01.45.51.049098 AM +07:00' from dual union all
      select '20-AUG-19 11.45.51.073921 PM +07:00' from dual union all
      select '21-AUG-19 01.45.51.064047 AM +07:00' from dual union all 
      select '21-AUG-19 11.45.51.073921 PM +07:00' from dual union all
      select '22-AUG-19 01.45.51.073921 AM +07:00' from dual union all
      select '22-AUG-19 11.45.51.073921 PM +07:00' from dual 
    )
    select t.txn_dt_time, 
        To_Timestamp_tz(txn_dt_time,'dd-mon-yy hh12.mi.ss.ff6 AM TZH:TZM') as txn_dt_time_ts , 
        cast(To_Timestamp_tz(txn_dt_time,'dd-mon-yy hh12.mi.ss.ff6 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 hh12.mi.ss.ff6 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)


    Regards

    Stian

    Niall