CASE DATE

[cloudtwter]

SELECT
COL1,
CASE COL1
when (days(date(SUBSTR(COL1, 1, 4) || '-' || SUBSTR(COL1, 5, 2)|| '-' || SUBSTR(COL1, 7, 2)))
>
(days(current_date)-365))
THEN 'A'
ELSE 'B'
FROM TAB1;

COL1 is chat(8) storing as date for yyyymmdd

Trying to get less/greater than than 1 year old with case as A/B

 

[johnherman]

Probably need to convert the CHAR date to a number so that it can be used by DAYS. Is DAYS( expecting a number input? You are providing it with character data.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[cloudtwter]

Thanks,
I tried that earlier with to_number got error..

 

[fredericofonseca]

try

select COL1
     , case
       -- when days(TIMESTAMP_FORMAT(col1, 'YYYYMMDD')) > (days(current_date) - 365) -- this may work also
       when days(TO_DATE(col1, 'YYYYMMDD')) > (days(current_date) - 365)
       then 'A'
       else 'B'
       end as COL2
from TAB1;

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[mikrom]

this should work

select                                                       
  COL1,                                                      
  case                                                       
    when (days(date(substr(COL1,1,4)||'-'||                  
                    substr(COL1,5,2)||'-'||substr(COL1,7,2)))
            >  (days(CURRENT_DATE)-365))                     
    then 'A'                                                 
    else 'B'                                                 
  end                                                        
from TAB1

I tried it with this

with TAB1(COL1) as (                                         
  values ('20200405'),                                       
         ('20190305')                                        
)                                                            
select                                                       
  COL1,                                                      
  case                                                       
    when (days(date(substr(COL1,1,4)||'-'||                  
                    substr(COL1,5,2)||'-'||substr(COL1,7,2)))
            >  (days(CURRENT_DATE)-365))                     
    then 'A'                                                 
    else 'B'                                                 
  end                                                        
from TAB1

and it works:

 

[cloudtwter]

Works...Thanks All.