Select where Date is stored as a numeric
Last Post 29 Jan 2013 02:38 AM by Craig Scargill. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Craig Scargill
New Member
New Member
Posts:27

--
25 Jan 2013 09:10 AM

Hi. I have inherited a file where the date is stored in DMY format in a 6 numberic field. Also I have noticed which is part of my problem leading 0s are missing for example 5th Decmeber 2012 is stored 51212.

What I want to do is select where the date >= 01/11/11 (1st November 2011) The following select works on the dates that dont have a leading 0 but I cant get the where to work

Works

Select csosld, csonme,                      
date((substr(char(csolad),5,2) || '/' ||    
      substr(char(csolad),3,2) || '/' ||    
      substr(char(csolad),1,2))) as activity
FROM tbldata/pfsold

Doesnt work

SELECT CSOSLD, CSONME                        
FROM tbldata/pfsold where                    
date((substr(char(csolad),5,2) || '/' ||     
      substr(char(csolad),3,2) || '/' ||     
      substr(char(csolad),1,2))) >= '11/01/01'

 

Thanks

novacancy
Basic Member
Basic Member
Posts:125
Avatar

--
25 Jan 2013 09:33 AM
create table qtemp/mytable (f1 char(6))     
           
insert into qtemp/mytable values(' 51212')  
       
select * from qtemp/mytable                 
F1       
 51212   

with c1 (mydate) as (                           
select replace(f1,' ','0') from qtemp/mytable )
select *                                        
from c1                                         
MYDATE    
051212    

with c1 (mydate) as (                            
select replace(f1,' ','0') from qtemp/mytable )  
select date('20'||substr(mydate,5,2)||           
             substr(mydate,3,2)||                
             substr(mydate,1,2)||'000000')       
from c1                                          

DATE

2012-12-05

HTH

Enduring the V5R3 blues
Craig Scargill
New Member
New Member
Posts:27

--
25 Jan 2013 09:48 AM
Thanks for your reply. This will convert my numeric to a date but how do I select all records where the date >= 20111101. Ideally I need to do the convert and where clause all as one step.

Thanks
B.Hauser
Veteran Member
Veteran Member
Posts:2050

--
25 Jan 2013 10:25 AM
Just try the following: Replace CHAR with DIGITS prefix the result with 20 and add '000000' at the end:
Select  CSOSLD, CSONME
From ...
Where Date('20' concat Substr(Digits(CSOLAD), 5, 2)
concat Substr(Digits(CSOLAD), 3, 2)
concat Substr(Digits(CSOLAD), 1, 2)
concat '000000') >= '2011-01-01';

Birgitta

 

Craig Scargill
New Member
New Member
Posts:27

--
28 Jan 2013 08:38 AM
Thanks Birgitta. That works except I have found some old records with dates in the 90s. Is there a way I can wrap a case statement around it so that if substr(csolad,5,2) > 90 then '19' else '20'. I have tried but cant get the syntax correct. Thanks
B.Hauser
Veteran Member
Veteran Member
Posts:2050

--
28 Jan 2013 10:28 AM

Try the following

 
Select CSOSLD, CSONME
From ...
Where Date(Case When Substr(Digits(CSOLAD, 5, 2) >= '40'
Then '19'
Else '20' End
concat Substr(Digits(CSOLAD), 5, 2)
concat Substr(Digits(CSOLAD), 3, 2)
concat Substr(Digits(CSOLAD), 1, 2)
concat '000000') >= '2011-01-01';
Craig Scargill
New Member
New Member
Posts:27

--
29 Jan 2013 02:38 AM
Thank you. It did exactly what I wanted
You are not authorized to post a reply.

Acceptable Use Policy