https:  ## Message Posted: Wed, 29 Sep 2004 @ 16:19:42 GMT  < 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.  < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2004 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback 