Syntax Error-Help!!

[Ty]

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

[Ian]

Ty wrote:

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'

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.

[Ty]

On Mar 14, 1:15*pm, Ian <[e-mail]:

Ty wrote:

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'

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.- Hide quoted text -

- Show quoted text -

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

[Mark A]

"Ian" <ia*****@mobileaudio.comwrote in message
news:7h*******************@newsfe14.phx...

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.

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.

[Ty]

On Mar 14, 4:06*pm, "Mark A" <[e-mail]:

"Ian" <[e-mail] in message

news:7h*******************@newsfe14.phx...

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.

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.

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?

[Knut Stolze]

Ty wrote:

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

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany