PARSEDATETIME and invalid Date

[Frenz]

Hi, i'm using PARSEDATETIME function to do some calcs...the problem is
that this date is not always valorized.
So if I do a

SELECT
(DATEDIFF( 'DAY', CURRENT_DATE(), PARSEDATETIME( dataDecorrenza,
'yyyyMMdd'))/30)
....

when it founds an empty or null dataDecorrenza stops throwing an
exception:
Caused by: org.h2.jdbc.JdbcSQLException: Error parsing [90014-72]
at org.h2.message.Message.getSQLException(Message.java:92)
at org.h2.util.StringUtils.parseDateTime(StringUtils.java:399)
at org.h2.expression.Function.getValueWithArgs(Function.java:972)
at org.h2.expression.Function.getValue(Function.java:371)
at org.h2.expression.Function.getNullOrValue(Function.java:378)
at org.h2.expression.Function.getValueWithArgs(Function.java:792)
at org.h2.expression.Function.getValue(Function.java:371)
at org.h2.expression.Operation.getValue(Operation.java:97)
at org.h2.expression.Alias.getValue(Alias.java:36)

Caused by: java.text.ParseException: Unparseable date: ""
at java.text.DateFormat.parse(Unknown Source)
at org.h2.util.StringUtils.parseDateTime(StringUtils.java:396)


I'm using MySQL too...and I made the same query on it. Here it works
returning a very high number. (if dataDecorrenza has an invalid value
i suppose it is considered as 1970)

SELECT
(DATEDIFF( CURRENT_DATE(), str_to_date( dataDecorrenza,\"%Y%m%D\"))/
30)";

Is there a way to make h2db to work in this manner? (..or should i
recompile the source catching the exception?)

Thanks,

Frenz

[Thomas Mueller]

Hi,

H2 supports the DATEDIFF function as HSQLDB and MS SQL Server. See also
http://msdn.microsoft.com/en-us/library/ms189794.aspx
MySQL also supports this method but it is slightly different.

> when it founds an empty or null dataDecorrenza stops throwing an exception

It seems that MySQL doesn't throw an error when parsing invalid dates.
I think this is not what I want for H2. I am sorry if this is a
problem for you, but I believe silently ignoring errors is not a good
idea. What about if you use:

SET @DATE = '';
SELECT CASE WHEN @DATE='' THEN 0 ELSE
DATEDIFF('DAY', PARSEDATETIME(@DATE, 'yyyyMMdd'), CURRENT_DATE())/30 END;

Here are some tests I have made:

H2:
SELECT PARSEDATETIME('20041231', 'yyyyMMdd');
// 2004-12-31 00:00:00.0
SELECT PARSEDATETIME('', 'yyyyMMdd');
// Error parsing
SELECT PARSEDATETIME(NULL, 'yyyyMMdd');
// NULL
SELECT DATEDIFF('DAY', CURRENT_DATE(), PARSEDATETIME('20041231',
'yyyyMMdd'))/30;
// -41
SELECT DATEDIFF('DAY', PARSEDATETIME('20041231', 'yyyyMMdd'),
CURRENT_DATE())/30;
// 40

MySQL:
SELECT str_to_date('20041231','%Y%m%D');
// 2004-12-31
SELECT str_to_date('','%Y%m%D');
// Value '0000-00-00' can not be represented as java.sql.Date S1009/0
SELECT '=' || str_to_date('','%Y%m%D');
// 0 (BigInt)
SELECT str_to_date(NULL,'%Y%m%D');
// NULL
SELECT DATEDIFF(CURRENT_DATE(), str_to_date('20041231','%Y%m%D'))/30;
// 41.4000
SELECT DATEDIFF(str_to_date('20041231','%Y%m%D'), CURRENT_DATE())/30;
// -41.4000

HSQLDB:
CALL DATEDIFF('DAY', NOW(), '2004-12-31')/30;
// -41
CALL DATEDIFF('DAY', '2004-12-31', NOW())/30;
// 41

Regards,
Thomas

[Frenz]

Hi, thanks for the answer...but the problem is not in DATEDIFF
function but in PARSEDATETIME. When the date is unparseable, an
exception is thrown and the query is interrupted.
IMO it should use a default date in the catch clause (like MySQL do)
or eventually skip the unvalid record.
I could modify the source code and recompile...but I don't like this
option

Frenz

On 26 Mag, 21:41, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
> Hi,
>
> H2 supports the DATEDIFF function as HSQLDB and MS SQL Server. See alsohttp://msdn.microsoft.com/en-us/library/ms189794.aspx

[Thomas Mueller]

Hi,

> Hi, thanks for the answer...but the problem is not in DATEDIFF
> function but in PARSEDATETIME.

I know.

> I could modify the source code and recompile...but I don't like this option

H2 doesn't support str_to_date, so you anyway need to modify the
source code. For better MySQL compatibility, you could create your own
Java function STR_TO_DATE that ignores errors. See also CREATE ALIAS
at http://www.h2database.com/html/grammar.html#createalias

> when it founds an empty or null dataDecorrenza stops throwing an exception

It seems that MySQL doesn't throw an error when parsing invalid dates.
I think this is not what I want for H2. I am sorry if this is a
problem for you, but I believe silently ignoring errors is not a good

idea. Another solution is:

SET @DATE = '';
SELECT CASE WHEN @DATE='' THEN 0 ELSE DATEDIFF('DAY',
PARSEDATETIME(@DATE, 'yyyyMMdd'), CURRENT_DATE())/30 END;

Regards,
Thomas

[Frenz]

Thank you very much!!!
I'll try both solutions and see which is better for me.

Bye,

Frenz


On 27 Mag, 19:57, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> > Hi, thanks for the answer...but the problem is not in DATEDIFF
> > function but in PARSEDATETIME.
>
> I know.
>
> > I could modify the source code and recompile...but I don't like this option
>
> H2 doesn't support str_to_date, so you anyway need to modify the
> source code. For better MySQL compatibility, you could create your own
> Java function STR_TO_DATE that ignores errors. See also CREATE ALIAS

> athttp://www.h2database.com/html/grammar.html#createalias