Syntax Error-Help!!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ty - 14 Mar 2008 17:42 GMT

Syntax Error-Help!!

My error "SQL0180N The syntax of the string representation of a
datetime value is incorrect. SQLSTATE=22007" .

I'm using yyyy-mm-dd. I can see the dates in a column on another
query as 2007-06-17 23:00:13.827584 which is yyyy-mm-dd time(i
guess).

My attempt:
SELECT <column_list>
FROM ABXDB01.mdall_rec_holdg
WHERE ABX_REQ_BUCKT = 'OK'
AND CMPL_Q_DT > '2008-03-01';

2nd attempt:
AND CMPL_Q_DT > '2008-03-01'

3rd attempt:
AND CMPL_Q_DT > '2008-03-01%';

4th attempt without the DT works ok:

SELECT <column_list>
FROM ABXDB01.mdg_req_holdg
WHERE ABX_REQ_BUCKT = 'OK'

Any ideas?

Thanks,
Ty

Reply to this Message

Lennart - 14 Mar 2008 18:50 GMT

> Syntax Error-Help!!
>
[quoted text clipped - 10 lines]
> WHERE ABX_REQ_BUCKT = 'OK'
> AND CMPL_Q_DT > '2008-03-01';

I assume CMPL_Q_DT is a timestamp. Try:

[...]
AND date(CMPL_Q_DT) > '2008-03-01';

/Lennart

Reply to this Message

Mark A - 14 Mar 2008 19:10 GMT

> I assume CMPL_Q_DT is a timestamp. Try:
>
> [...]
> AND date(CMPL_Q_DT) > '2008-03-01';
>
> /Lennart

For performance reasons (in certain situations), you might want to try this:

AND date(CMPL_Q_DT) > '2008-03-01 00:00:00.000000' ;

date(CMPL_Q_DT) might prevent DB2 from using an index on that column.

Reply to this Message

Ian - 14 Mar 2008 19:15 GMT

> Syntax Error-Help!!
>
[quoted text clipped - 22 lines]
> FROM ABXDB01.mdg_req_holdg
> WHERE ABX_REQ_BUCKT = 'OK'

DB2 interprets '2008-03-01' as a date, and CMPL_Q_DT is
(presumably) a timestamp. So, use either:

CMPL_Q_DT > '2008-03-01-00.00.00.000000'

or

DATE(CMPL_Q_DT) > '2008-03-01'

The latter form will preclude the use of any index on the
CMPL_Q_DT column, so you're better off specifying the full
timestamp.

Reply to this Message

Ty - 14 Mar 2008 20:27 GMT

> > Syntax Error-Help!!
>
[quoted text clipped - 37 lines]
>
> - Show quoted text -

Got it!!! It is a timestamp. I used the '2008-03-01-00.00.00.000000'

Reply to this Message

Mark A - 14 Mar 2008 22:06 GMT

> DB2 interprets '2008-03-01' as a date, and CMPL_Q_DT is
> (presumably) a timestamp. So, use either:
[quoted text clipped - 8 lines]
> CMPL_Q_DT column, so you're better off specifying the full
> timestamp.

Actually, the two above choices are not equivalent (I made the same error in
my post). These would be the same:

CMPL_Q_DT >= '2008-03-02-00.00.00.000000'
DATE(CMPL_Q_DT) > '2008-03-01'

But I am not sure of the intent of the OP.

Reply to this Message

Ty - 28 Apr 2008 15:05 GMT

> > DB2 interprets '2008-03-01' as a date, and CMPL_Q_DT is
> > (presumably) a timestamp. So, use either:
[quoted text clipped - 16 lines]
>
> But I am not sure of the intent of the OP.

Thanks for all of the help...

SELECT <column_list>
FROM ABXDB01.mdg_req_holdg
WHERE ABX_REQ_BUCKT = 'OK'

Is it possible to use "*" wildcard to display everything for
ABX_REQ_BUCKT?

Reply to this Message

Knut Stolze - 28 Apr 2008 19:05 GMT

> SELECT <column_list>
> FROM ABXDB01.mdg_req_holdg
> WHERE ABX_REQ_BUCKT = 'OK'
>
> Is it possible to use "*" wildcard to display everything for
> ABX_REQ_BUCKT?

SELECT DISTINCT abx_req_buckt
FROM abxdb01.mdg_req_holdg

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message