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

Attachments

Library

Quick Reference