Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CASE DATE

Status
Not open for further replies.

cloudtwter

Technical User
Apr 3, 2020
3
AU
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
 
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


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


 
try

Code:
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

FAQ219-2884
FAQ181-2886
 
this should work
Code:
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
Code:
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:
Screenshot_at_2020-04-06_00-06-34_jp5vco.png
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top