CONVERT STRING INTO DATE 1

[cristi22]

Hello all -

I have to use the date for the select criteria, & the following works for 6 digits period (200501), but does not for the 4 digits period (9901)

  AND DATE( SUBSTR(CONT_DETAIL.WORK_PERIOD,1,4)|| '-'|| 
         SUBSTR(CONT_DETAIL.WORK_PERIOD,5,2)|| '-01') 	= DATE('2005-09-01') 

	 
	AND DATE(SUBSTR(CONT_DETAIL.WORK_PERIOD,1,4)|| '-'|| 
         SUBSTR(CONT_DETAIL.WORK_PERIOD,5,2)|| '-01') = DATE('2005-09-01')

thanks,
cristi

 

[cristi22]

I changed the data to 6 bytes, any solutions for education are more than welcome!

 

[ddiamond]

It might get messy, but you could try:

(CASE WHEN LENGTH(CONT_DETAIL.WORK_PERIOD)=6
  THEN DATE(SUBSTR(CONT_DETAIL.WORK_PERIOD,1,4)|| '-'|| SUBSTR(CONT_DETAIL.WORK_PERIOD,5,2)|| '-01')  
  ELSE DATE('19' || SUBSTR(CONT_DETAIL.WORK_PERIOD,1,2)|| '-'|| SUBSTR(CONT_DETAIL.WORK_PERIOD,3,2)|| '-01')  
  END)

I think fixing the data to be consistent is a better idea, however.

 

[ddiamond]

I'd probably put the above code in a user defined function. That's what I ended up doing for our AS400 because it also stored dates in a funny fashion.