Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 29 Sep 2004 @ 16:19:42 GMT


     
  <Prev Next>  
<<First
<Prev
Next>
Last>>
 


Subj:   Is this Problem with CAST or SUBSTR or CURRENT_DATE?
 
From:   Kambhoji, Naveen Ram Prasanna

Hi Group,

I have just fired the following queries which gives me two different results, Can any one explain the cause of this?

     SELECT CAST(
     CASE
     WHEN EXTRACT(DAY FROM CURRENT_DATE ) <=7 THEN SUBSTR(CURRENT_DATE,1,4)
     || SUBSTR(CURRENT_DATE,6,2) || '1'
     WHEN (EXTRACT(DAY FROM CURRENT_DATE) > 7 AND EXTRACT(DAY FROM
     CURRENT_DATE) <=14) THEN SUBSTR(CURRENT_DATE,1,4) ||
     SUBSTR(CURRENT_DATE,6,2) || '2'
     WHEN (EXTRACT(DAY FROM CURRENT_DATE) >14 AND EXTRACT(DAY FROM
     CURRENT_DATE) <=21) THEN SUBSTR(CURRENT_DATE,1,4) ||
     SUBSTR(CURRENT_DATE,6,2) || '3'
     ELSE SUBSTR(CURRENT_DATE,1,4) || SUBSTR(CURRENT_DATE,6,2) || '4' END
     AS INTEGER
     ) AS STAGE_WEEK_ID;

     Result : 4024


     SELECT CAST(
     (
     CASE
     WHEN EXTRACT(DAY FROM CURRENT_DATE ) <=7 THEN SUBSTR(CURRENT_DATE,1,4)
     || SUBSTR(CURRENT_DATE,6,2) || '1'
     WHEN (EXTRACT(DAY FROM CURRENT_DATE) > 7 AND EXTRACT(DAY FROM
     CURRENT_DATE) <=14) THEN SUBSTR(CURRENT_DATE,1,4) ||
     SUBSTR(CURRENT_DATE,6,2) || '2'
     WHEN (EXTRACT(DAY FROM CURRENT_DATE) >14 AND EXTRACT(DAY FROM
     CURRENT_DATE) <=21) THEN SUBSTR(CURRENT_DATE,1,4) ||
     SUBSTR(CURRENT_DATE,6,2) || '3'
     ELSE SUBSTR(CURRENT_DATE,1,4) || SUBSTR(CURRENT_DATE,6,2) || '4' END
     )
     AS INTEGER
     )

     Result : 2004094

Regards,

Naveen Ram Prasanna,
CTS,Chennai.



     
  <Prev Next>  
<<First
<Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016