By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,924 Members | 1,793 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,924 IT Pros & Developers. It's quick & easy.

CASTLESCE

P: n/a
Hello,

I need to cleanse some data. Some of the data consists of CHARs of the
form '2006-12-24' which are to be converted to DATE values.

Some of the values are known to be invalid (such as '0000-00-00' or
'2006-02-45'), and those values need to be converted to NULLs.

So I'm looking for a "CASTLESCE" function: Cast to a type; if the cast
fails, return NULL.

My first thought was to create a UDF where the UDF catches cast-errors via
a declared continue-handler. But using a continue-handler seems to be
possible only in procedures(?).

What's my best option? Try using a procedure, or in some other way?

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Feb 9 '07 #1
Share this Question
Share on Google+
24 Replies


P: n/a
Troels Arvin wrote:
Hello,

I need to cleanse some data. Some of the data consists of CHARs of the
form '2006-12-24' which are to be converted to DATE values.

Some of the values are known to be invalid (such as '0000-00-00' or
'2006-02-45'), and those values need to be converted to NULLs.

So I'm looking for a "CASTLESCE" function: Cast to a type; if the cast
fails, return NULL.

My first thought was to create a UDF where the UDF catches cast-errors via
a declared continue-handler. But using a continue-handler seems to be
possible only in procedures(?).

What's my best option? Try using a procedure, or in some other way?
You can use a SQL UDF that calls a procedure..
You won't get a prize for performance though....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 9 '07 #2

P: n/a
On Fri, 09 Feb 2007 08:22:38 -0500, Serge Rielau wrote:
>What's my best option? Try using a procedure, or in some other way?
You can use a SQL UDF that calls a procedure..
You won't get a prize for performance though....
How bad is it - will it be more rational to dump the data, massage it
procedurally (e.g. with perl), and then import it again?

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Feb 9 '07 #3

P: n/a
Troels Arvin wrote:
On Fri, 09 Feb 2007 08:22:38 -0500, Serge Rielau wrote:
>>What's my best option? Try using a procedure, or in some other way?
You can use a SQL UDF that calls a procedure..
You won't get a prize for performance though....

How bad is it - will it be more rational to dump the data, massage it
procedurally (e.g. with perl), and then import it again?
*lol* No it will be better than that...
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Feb 9 '07 #4

P: n/a
>
What's my best option? Try using a procedure, or in some other way?
Hello.
You should use an external UDF.
For example, in java it whould be fairly simple code.

Sincerely,
Mark B.

Feb 9 '07 #5

P: n/a
On Fri, 09 Feb 2007 06:39:22 -0800, 4.spam wrote:
>What's my best option? Try using a procedure, or in some other way?
You should use an external UDF.
For example, in java it whould be fairly simple code.
Yes. But I'm afraid of external UDFs:
- are external UDFs backed up when the database is
backed up? (using TSM)
- what will happen if/when we upgrade from DB2 v. 8 to v. 9?

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Feb 9 '07 #6

P: n/a
Troels Arvin wrote:
Hello,

I need to cleanse some data. Some of the data consists of CHARs of the
form '2006-12-24' which are to be converted to DATE values.

Some of the values are known to be invalid (such as '0000-00-00' or
'2006-02-45'), and those values need to be converted to NULLs.
If there are just a few of such values, then maybe NULLIF is a way to go?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Feb 9 '07 #7

P: n/a
Yes. But I'm afraid of external UDFs:
- are external UDFs backed up when the database is
backed up? (using TSM)
AFAIK, no.
- what will happen if/when we upgrade from DB2 v. 8 to v. 9?
I whould recompile all external routines (C, JAVA) except those with
PARAMETER STYLE JAVA.

Feb 9 '07 #8

P: n/a
On Feb 9, 12:06 pm, Troels Arvin <tro...@arvin.dkwrote:
Hello,

I need to cleanse some data. Some of the data consists of CHARs of the
form '2006-12-24' which are to be converted to DATE values.

Some of the values are known to be invalid (such as '0000-00-00' or
'2006-02-45'), and those values need to be converted to NULLs.

So I'm looking for a "CASTLESCE" function: Cast to a type; if the cast
fails, return NULL.

My first thought was to create a UDF where the UDF catches cast-errors via
a declared continue-handler. But using a continue-handler seems to be
possible only in procedures(?).

What's my best option? Try using a procedure, or in some other way?
Hi Troels, you can use a calendar table in the process. Don't know if
its the best option but it may serve your needs:

[lelle@53dbd181 lelle]$ db2 "create table calendar (d date not null
primary key)"
[lelle@53dbd181 lelle]$ db2 "insert into calendar with t (d) as
(values current_date - 1 year union all select d + 1 day from t where
d < current_date + 1 year) select * from t"

start date and stop date should be chosen so that it spans your date
interval

Assuming your source table like:

[lelle@53dbd181 lelle]$ db2 "create table X (mydate char(10) not
null)"
DB20000I The SQL command completed successfully.
[lelle@53dbd181 lelle]$
[lelle@53dbd181 lelle]$ db2 "insert into X values ('2006-12-24'),
('0000-00-00') ,('2006-02-45') "

and target table

[lelle@53dbd181 lelle]$ db2 "create table Y (newdate date not null)"
DB20000I The SQL command completed successfully.
[lelle@53dbd181 lelle]$ db2 "insert into Y select * from X where
mydate in (select char(d) from calendar)"
DB20000I The SQL command completed successfully.
[lelle@53dbd181 lelle]$ db2 "select * from Y"

NEWDATE
----------
2006-12-24

1 record(s) selected.

You probably have a different cleaning process :-), but the idea
should work for other scenarios as well.
HTH
/Lennart
Feb 9 '07 #9

P: n/a
On Fri, 09 Feb 2007 15:52:32 +0100, Knut Stolze wrote:
If there are just a few of such values, then maybe NULLIF is a way to go?
How could NULLIF be helpful in this case?

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Feb 9 '07 #10

P: n/a
On Fri, 09 Feb 2007 11:51:55 -0800, Lennart wrote:
Hi Troels, you can use a calendar table in the process.
Hmm; yes, I see that this should work, although I find it a bit
"hack'ish". Thanks. My dates probably span at least a century, but then
again, a date table with around 40000 rows isn't scary.

I'll use this solution if I don't find a way to exploit DB2's
CAST.

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Feb 9 '07 #11

P: n/a
On Feb 9, 9:23 pm, Troels Arvin <tro...@arvin.dkwrote:
[...]
>Hmm; yes, I see that this should work, although I find it a bit
"hack'ish". Thanks. My dates probably span at least a century, but then
again, a date table with around 40000 rows isn't scary.
To improve speed you can generate the calendar table in char format.
With an index on the column, lookup should not be a problem.

Don't know if it is the calendar table that you think is "hack'ish",
but I find a calendar useful in many situations. A typical example is
when one want to count the number of things that happened over a
period of time. By doing an outer join with the calendar one gets 0
for non existing dates in the fact table. Therefor I often generate
one, and use it when ever the need comes up.
I'll use this solution if I don't find a way to exploit DB2's
CAST.
Please post a solution if you do. I tried to think about it for a
while, but nothing pops up.

/Lennart

Feb 10 '07 #12

P: n/a
How about to check data by case expressions like this?

-------------------- Commands Entered ------------------------------
SELECT CharDate
, CASE
WHEN SUBSTR(CharDate,1,4) BETWEEN '0001' AND '9999'
AND SUBSTR(CharDate,6,2) BETWEEN '01' AND '12' THEN
CASE
WHEN CharDate
BETWEEN SUBSTR(CharDate,1,8)||'01'
AND CHAR(DATE(SUBSTR(CharDate,1,8)||'01') + 1
MONTH - 1 DAY) THEN
DATE(CharDate)
ELSE NULL
END
ELSE NULL
END ValidDate
FROM (VALUES '0000-00-00'
, '2006-00-15'
, '2006-13-15'
, '2006-02-45'
, '2005-02-29'
, '2008-02-29'
, '2006-01-01'
, '2006-03-31'
, '2006-12-31'
) TestData(CharDate);
--------------------------------------------------------------------

CHARDATE VALIDDATE
---------- ----------
0000-00-00 -
2006-00-15 -
2006-13-15 -
2006-02-45 -
2005-02-29 -
2008-02-29 2008-02-29
2006-01-01 2006-01-01
2006-03-31 2006-03-31
2006-12-31 2006-12-31

9 record(s) selected.

Feb 10 '07 #13

P: n/a
Correction to handle '9999-12-xx':
SELECT CharDate
, CASE
WHEN SUBSTR(CharDate,1,4) BETWEEN '0001' AND '9999'
AND SUBSTR(CharDate,6,2) BETWEEN '01' AND '12' THEN
CASE
WHEN CharDate
BETWEEN SUBSTR(CharDate,1,8)||'01'
AND COALESCE(CHAR(DATE(NULLIF(SUBSTR(CharDate,
1,8),'9999-12-')||'01') + 1 MONTH - 1 DAY)
,'9999-12-31') THEN
DATE(CharDate)
ELSE NULL
END
ELSE NULL
END ValidDate
FROM (VALUES '0000-00-00'
, '2006-00-15'
, '2006-13-15'
, '2006-02-45'
, '2005-02-29'
, '2008-02-29'
, '2006-01-00'
, '2006-01-01'
, '2006-04-31'
, '2006-12-00'
, '2006-12-31'
, '9999-11-15'
, '9999-12-31'
) TestData(CharDate)

Feb 10 '07 #14

P: n/a
On Sat, 10 Feb 2007 05:17:07 -0800, Tonkuma wrote:
Correction to handle '9999-12-xx':
[...]

I dislike the fact that this code isn't trivial to verify for correctness.
But I like the fact that it uses DB2's built-in date arithmetics to do
calculations. And I like the fact that it can handle dates in a very broad
interval.

Thanks.

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Feb 10 '07 #15

P: n/a
Hello,

On Sat, 10 Feb 2007 04:08:23 -0800, Lennart wrote:
Don't know if it is the calendar table that you think is "hack'ish",
Yes, because it buts limits on the dates that may be checked; but I guess
that proper date checks will always work within boundaries.

Your suggestion makes me think that it would probably be useful to have a
general "util" schema with stuff like dates, zip codes, ISO country codes,
etc. Does someone know of a good collection of information like this? -
Sort of a machine readable wikipedia (with limited scope)?

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Feb 10 '07 #16

P: n/a
>I see that this should work, although I find it a bit "hack'ish". Thanks. My dates probably span at least a century, but then again, a date table with around 40000 rows isn't scary. <<

As a slightly off-topic reamrk, I am workignon my next book right now
and it deals with table-oriented solutions rather than computational
solutions to DB problems.

If anyone has more tips like this, please send them to me.

My model of the future of RDBMS is that we will have multi-core chips
(INTEL is promising 80 in the near future) combined with secondary
storage that has the seek times of current semi-conductor primary
storage. This will make massively parallel RDBMS the norm.

Decades ago Jerry Pournelle predicted that we would have one processor
per task in a system (he was thinking about smart printers or the IBM
tape drive that has encryption). I am starting to see the day when we
have one processor per row or usbset of a table, which pass their
results up a hierarchy that mimics a parse tree in parallel.

In this case, each of the 80 processors handles about 50 date
strings. Your refrigerator CPU handles more data than that!

Computations are much harder than joins, so the furure belongs to
simple theta operators that can be expressed in a few low level
commands in a processor unit.

Comments? Feedback? Or should I start a new thread (probably in Comp
Theory)?

Feb 10 '07 #17

P: n/a
>I see that this should work, although I find it a bit "hack'ish". Thanks. My dates probably span at least a century, but then again, a date table with around 40000 rows isn't scary. <<

As a slightly off-topic reamrk, I am workignon my next book right now
and it deals with table-oriented solutions rather than computational
solutions to DB problems.

If anyone has more tips like this, please send them to me.

My model of the future of RDBMS is that we will have multi-core chips
(INTEL is promising 80 in the near future) combined with secondary
storage that has the seek times of current semi-conductor primary
storage. This will make massively parallel RDBMS the norm.

Decades ago Jerry Pournelle predicted that we would have one processor
per task in a system (he was thinking about smart printers or the IBM
tape drive that has encryption). I am starting to see the day when we
have one processor per row or usbset of a table, which pass their
results up a hierarchy that mimics a parse tree in parallel.

In this case, each of the 80 processors handles about 50 date
strings. Your refrigerator CPU handles more data than that!

Computations are much harder than joins, so the furure belongs to
simple theta operators that can be expressed in a few low level
commands in a processor unit.

Comments? Feedback? Or should I start a new thread (probably in Comp
Theory)?

Feb 10 '07 #18

P: n/a
On Fri, 09 Feb 2007 08:22:38 -0500, Serge Rielau wrote:
>My first thought was to create a UDF where the UDF catches cast-errors via
a declared continue-handler. But using a continue-handler seems to be
possible only in procedures(?).

What's my best option? Try using a procedure, or in some other way?
You can use a SQL UDF that calls a procedure..
You won't get a prize for performance though....
I finally managed to get things working this way:

-- ================================================== ================
-- Called by the "castalesce_date" function; not intented to
-- be called directly
CREATE PROCEDURE castalesce_date_check_(IN strval VARCHAR(100))
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
BEGIN
DECLARE retval INT DEFAULT 1;
DECLARE dateval DATE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET retval=-1;
SET dateval=DATE(strval);
RETURN retval;
END@

CREATE FUNCTION castalesce_date(strval VARCHAR(100))
RETURNS DATE
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
BEGIN ATOMIC
DECLARE retval INT;
-- The following is needed because declaration of SQLEXECPTION handlers
-- isn't allowed in UDFs:
CALL castalesce_date_check_(strval);
GET DIAGNOSTICS retval = DB2_RETURN_STATUS;
IF retval = 1 THEN RETURN DATE(strval);
ELSE RETURN NULL;
END IF;
END@

-- Note: The result from the stored procedure is transferred via the
-- return value. First, I tried doing it via an OUT parameter, but
-- setting the OUT parameter required me to declare the procedure
-- MODIFIES SQL DATA. Calling such a procedure would make the caller
-- (the UDF) have status MODIFIES SQL DATA, as well. And it _seems_
-- (not to be read anywhere in the DB2 docs, at least not where I
-- could find it) UDFs can't be declared with MODIFIES SQL DATA
-- unless it's a "table function" (what I need is a "scalar" function,
-- as the above).
-- ================================================== ================

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Feb 10 '07 #19

P: n/a
On Feb 10, 9:19 pm, Troels Arvin <tro...@arvin.dkwrote:
On Fri, 09 Feb 2007 08:22:38 -0500, Serge Rielau wrote:
My first thought was to create a UDF where the UDF catches cast-errors via
a declared continue-handler. But using a continue-handler seems to be
possible only in procedures(?).
What's my best option? Try using a procedure, or in some other way?
You can use a SQL UDF that calls a procedure..
You won't get a prize for performance though....

I finally managed to get things working this way:

-- ================================================== ================
-- Called by the "castalesce_date" function; not intented to
-- be called directly
CREATE PROCEDURE castalesce_date_check_(IN strval VARCHAR(100))
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
BEGIN
DECLARE retval INT DEFAULT 1;
DECLARE dateval DATE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET retval=-1;
SET dateval=DATE(strval);
RETURN retval;
END@

CREATE FUNCTION castalesce_date(strval VARCHAR(100))
RETURNS DATE
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
BEGIN ATOMIC
DECLARE retval INT;
-- The following is needed because declaration of SQLEXECPTION handlers
-- isn't allowed in UDFs:
CALL castalesce_date_check_(strval);
GET DIAGNOSTICS retval = DB2_RETURN_STATUS;
IF retval = 1 THEN RETURN DATE(strval);
ELSE RETURN NULL;
END IF;
END@

-- Note: The result from the stored procedure is transferred via the
-- return value. First, I tried doing it via an OUT parameter, but
-- setting the OUT parameter required me to declare the procedure
-- MODIFIES SQL DATA. Calling such a procedure would make the caller
-- (the UDF) have status MODIFIES SQL DATA, as well. And it _seems_
-- (not to be read anywhere in the DB2 docs, at least not where I
-- could find it) UDFs can't be declared with MODIFIES SQL DATA
-- unless it's a "table function" (what I need is a "scalar" function,
-- as the above).
-- ================================================== ================
If you have the opportunity, Is there any difference in performance
between castalesce_date and castalesce_date2 below? I've been told
that there are performance issues doing a call to a procedure as above
(Serges comment indicate this as well), and I'm curious whether this
is true in your case. I could generate some data on my own, but I have
a feeling that you've got more than enough :-)

/Lennart

DROP FUNCTION lelle.castalesce_date2 @

drop table lelle.calendar @
create table lelle.calendar (
calendar_date date not null,
calendar_chardate char(10) not null
generated always as (char(calendar_date))
) @

create unique index lelle.xpk_calendar on calendar (calendar_date)
cluster allow reverse scans @

alter table lelle.calendar add constraint xpk_calendar
primary key (calendar_date) @

create unique index lelle.xak_calendar on calendar (calendar_chardate)
include (calendar_date) allow reverse scans @

insert into lelle.calendar (calendar_date)
with t (d) as (
values current_date - 200 years
union all
select d + 1 day from t
where d + 1 day < current_date + 200 years)
select * from t @

runstats on table lelle.calendar for indexes all @

CREATE FUNCTION lelle.castalesce_date2(strval VARCHAR(100))
RETURNS DATE
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
RETURN
select c.calendar_date from calendar c
where c.calendar_chardate = strval
@

Feb 10 '07 #20

P: n/a
On Feb 10, 11:52 pm, "Lennart" <Erik.Lennart.Jons...@gmail.comwrote:
On Feb 10, 9:19 pm, Troels Arvin <tro...@arvin.dkwrote:
On Fri, 09 Feb 2007 08:22:38 -0500, Serge Rielau wrote:
>My first thought was to create a UDF where the UDF catches cast-errors via
>a declared continue-handler. But using a continue-handler seems to be
>possible only in procedures(?).
>What's my best option? Try using a procedure, or in some other way?
You can use a SQL UDF that calls a procedure..
You won't get a prize for performance though....
I finally managed to get things working this way:
-- ================================================== ================
-- Called by the "castalesce_date" function; not intented to
-- be called directly
CREATE PROCEDURE castalesce_date_check_(IN strval VARCHAR(100))
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
BEGIN
DECLARE retval INT DEFAULT 1;
DECLARE dateval DATE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET retval=-1;
SET dateval=DATE(strval);
RETURN retval;
END@
CREATE FUNCTION castalesce_date(strval VARCHAR(100))
RETURNS DATE
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
BEGIN ATOMIC
DECLARE retval INT;
-- The following is needed because declaration of SQLEXECPTION handlers
-- isn't allowed in UDFs:
CALL castalesce_date_check_(strval);
GET DIAGNOSTICS retval = DB2_RETURN_STATUS;
IF retval = 1 THEN RETURN DATE(strval);
ELSE RETURN NULL;
END IF;
END@
-- Note: The result from the stored procedure is transferred via the
-- return value. First, I tried doing it via an OUT parameter, but
-- setting the OUT parameter required me to declare the procedure
-- MODIFIES SQL DATA. Calling such a procedure would make the caller
-- (the UDF) have status MODIFIES SQL DATA, as well. And it _seems_
-- (not to be read anywhere in the DB2 docs, at least not where I
-- could find it) UDFs can't be declared with MODIFIES SQL DATA
-- unless it's a "table function" (what I need is a "scalar" function,
-- as the above).
-- ================================================== ================

If you have the opportunity, Is there any difference in performance
between castalesce_date and castalesce_date2 below? I've been told
that there are performance issues doing a call to a procedure as above
(Serges comment indicate this as well), and I'm curious whether this
is true in your case. I could generate some data on my own, but I have
a feeling that you've got more than enough :-)
Never mind, I populated a sample table and it appears that your
solution runs faster than mine, god dam it :-)

/Lennart

[...]

Feb 11 '07 #21

P: n/a
Weird. If I do this in the Command Editor and get the results in the Query
Results tab it works fine.

0000-00-00
2006-00-15
2006-13-15
2006-02-45
2005-02-29
2008-02-29 2008-02-29
2006-01-01 2006-01-01
2006-03-31 2006-03-31
2006-12-31 2006-12-31

But if I get the results in the Command Editor itself it's wrong!

CHARDATE VALIDDATE
---------- ----------
0000-00-00 -
2006-00-15 -
2006-13-15 -
2006-02-45 -
2005-02-29 -
2008-02-29 -
2006-01-00 -
2006-01-01 -
2006-04-31 -
2006-12-00 -
2006-12-31 -
9999-11-15 -
9999-12-31 12/31/9999

13 record(s) selected.
???

Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
>>Tonkuma<to*****@jp.ibm.com02/10/07 6:17 AM >>>
Correction to handle '9999-12-xx':
SELECT CharDate
, CASE
WHEN SUBSTR(CharDate,1,4) BETWEEN '0001' AND '9999'
AND SUBSTR(CharDate,6,2) BETWEEN '01' AND '12' THEN
CASE
WHEN CharDate
BETWEEN SUBSTR(CharDate,1,8)||'01'
AND COALESCE(CHAR(DATE(NULLIF(SUBSTR(CharDate,
1,8),'9999-12-')||'01') + 1 MONTH - 1 DAY)
,'9999-12-31') THEN
DATE(CharDate)
ELSE NULL
END
ELSE NULL
END ValidDate
FROM (VALUES '0000-00-00'
, '2006-00-15'
, '2006-13-15'
, '2006-02-45'
, '2005-02-29'
, '2008-02-29'
, '2006-01-00'
, '2006-01-01'
, '2006-04-31'
, '2006-12-00'
, '2006-12-31'
, '9999-11-15'
, '9999-12-31'
) TestData(CharDate)

Feb 12 '07 #22

P: n/a
Weird. I got following results.
--------------------------------------------------------------------
connect to SAMPLE user db2admin using

Database Connection Information

Database server = DB2/NT 8.2.6
SQL authorization ID = DB2ADMIN
Local database alias = SAMPLE
A JDBC connection to the target has succeeded.
-------------------- Commands Entered ------------------------------
SELECT CharDate
, CASE
WHEN SUBSTR(CharDate,1,4) BETWEEN '0001' AND '9999'
AND SUBSTR(CharDate,6,2) BETWEEN '01' AND '12' THEN
CASE
WHEN CharDate
BETWEEN SUBSTR(CharDate,1,8)||'01'
AND COALESCE(CHAR(DATE(NULLIF(SUBSTR(CharDate,
1,8),'9999-12-')||'01') + 1 MONTH - 1 DAY)
,'9999-12-31') THEN
DATE(CharDate)
ELSE NULL
END
ELSE NULL
END ValidDate
FROM (VALUES '0000-00-00'
, '2006-00-15'
, '2006-13-15'
, '2006-02-45'
, '2005-02-29'
, '2008-02-29'
, '2006-01-00'
, '2006-01-01'
, '2006-04-31'
, '2006-12-00'
, '2006-12-31'
, '9999-11-15'
, '9999-12-31'
) TestData(CharDate)
;
--------------------------------------------------------------------

CHARDATE VALIDDATE
---------- ----------
0000-00-00 -
2006-00-15 -
2006-13-15 -
2006-02-45 -
2005-02-29 -
2008-02-29 2008-02-29
2006-01-00 -
2006-01-01 2006-01-01
2006-04-31 -
2006-12-00 -
2006-12-31 2006-12-31
9999-11-15 9999-11-15
9999-12-31 9999-12-31

13 record(s) selected.
If destination changed to Query Results page.
0000-00-00
2006-00-15
2006-13-15
2006-02-45
2005-02-29
2008-02-29 2008-02-29
2006-01-00
2006-01-01 2006-01-01
2006-04-31
2006-12-00
2006-12-31 2006-12-31
9999-11-15 9999-11-15
9999-12-31 9999-12-31

Feb 13 '07 #23

P: n/a
Character representation of DATE value is defferent by teritory code
of your application. To overcome this difference, you can specify
format name "ISO" in CHAR function like followings.

SELECT CharDate
, CASE
WHEN SUBSTR(CharDate,1,4) BETWEEN '0001' AND '9999'
AND SUBSTR(CharDate,6,2) BETWEEN '01' AND '12' THEN
CASE
WHEN CharDate
BETWEEN SUBSTR(CharDate,1,8)||'01'
AND COALESCE(CHAR(DATE(NULLIF(SUBSTR(CharDate,
1,8),'9999-12-')||'01') + 1 MONTH - 1 DAY, ISO)
,'9999-12-31') THEN
DATE(CharDate)
ELSE NULL
END
ELSE NULL
END ValidDate
FROM (VALUES '0000-00-00'
, '2006-00-15'
, '2006-13-15'
, '2006-02-45'
, '2005-02-29'
, '2008-02-29'
, '2006-01-00'
, '2006-01-01'
, '2006-04-31'
, '2006-12-00'
, '2006-12-31'
, '9999-11-15'
, '9999-12-31'
) TestData(CharDate)
;

Feb 13 '07 #24

P: n/a
That indeed appears to be the problem. When I use your new example it works
in both the Query Results tab and just the regular result screen.

Interesting!

Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
>>Tonkuma<to*****@jp.ibm.com02/13/07 2:08 AM >>>
Character representation of DATE value is defferent by teritory code
of your application. To overcome this difference, you can specify
format name "ISO" in CHAR function like followings.

SELECT CharDate
, CASE
WHEN SUBSTR(CharDate,1,4) BETWEEN '0001' AND '9999'
AND SUBSTR(CharDate,6,2) BETWEEN '01' AND '12' THEN
CASE
WHEN CharDate
BETWEEN SUBSTR(CharDate,1,8)||'01'
AND COALESCE(CHAR(DATE(NULLIF(SUBSTR(CharDate,
1,8),'9999-12-')||'01') + 1 MONTH - 1 DAY, ISO)
,'9999-12-31') THEN
DATE(CharDate)
ELSE NULL
END
ELSE NULL
END ValidDate
FROM (VALUES '0000-00-00'
, '2006-00-15'
, '2006-13-15'
, '2006-02-45'
, '2005-02-29'
, '2008-02-29'
, '2006-01-00'
, '2006-01-01'
, '2006-04-31'
, '2006-12-00'
, '2006-12-31'
, '9999-11-15'
, '9999-12-31'
) TestData(CharDate)
;

Feb 13 '07 #25

This discussion thread is closed

Replies have been disabled for this discussion.