CODEWITHSUNDEEP
teradata
user2843206
user2843206

Reputation: 15

Teradata min between two dates

Hi i am relatively new to teradata. I have a row with 2 dates. I need to get the min between these tow dates and show it as third date. The dates are in YYYYMMDD format. Here are all the posiblities

example table

    col1--date1--date2
  • 123--20140802--20140619
  • 124--20140802--0
  • 124--0--20140802
  • 125--0--0

I need my result set to be

    col1--date1--date2--min_date
  • 123--20140802--20140619--6/19/2014
  • 124--20140802--0--8/2/2014(non zero will be min_date)
  • 124--0--20140802--8/2/2014(non zero will be min_date)
  • 125--0--0--?

This is what i could come up with 

select col1, date1, date2, 
case 
     when date1 <> 0 and date2 = 0 then  cast((date1 - 19000000) as date)
     when date1  = 0 and date2 <> 0 then  cast((date2 - 19000000) as date)
     when date1 = 0 and date2  = 0 then cast(null as date)
     when date1  > date2 
            then 
                cast((date2 - 19000000) as date) 
            else 
                 cast((date1 - 19000000) as date)
end as min_date

This does give me result, but query is slow. I wanted to know if there was better and efficient way of doing this, please let me know. I also need to show the min_date in another format as below

col1--date1--date2--min_date--min_month

  • 123--20140802--20140619--6/19/2014--Jun, 2014
  • 124--20140802--0--8/2/2014--Aug, 2014
  • 124--0--20140802--8/2/2014--Aug, 2014
  • 125--0--0--null--null

    • For min_month i know i can use this if i know which of the dates to use

    cast(cast((date2 or date2 - 19000000) as date format 'MMM') as Char(3))
|| ', ' || 
cast(cast((date2 or date2 - 19000000) as date format 'YYYY') as Char(4))

    Any help is highly appreciated. Thanks in advance..

    Upvotes: 0

    Views: 4591

    Answers (1)

    dnoeth
    dnoeth

    Reputation: 60462

    The CASE can be simplified to

    CAST(CASE
        WHEN date1 = 0 AND date2 = 0 THEN NULL
        WHEN date1 > date2 THEN date1
        ELSE date2
     END - 19000000 AS DATE) AS min_date

    Formatting the date:

    min_date (FORMAT 'mmm,byyyy') (CHAR(9))

    Upvotes: 1

    Related Questions