SEO

#1 (**permalink**) 03-17-2008, 06:12 AM

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

#2 (**permalink**) 03-17-2008, 06:12 AM

On Mar 14, 5:42 pm, Ty <tyrone_...@yahoo.com> 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';
>

I assume CMPL_Q_DT is a timestamp. Try:

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

/Lennart

#3 (**permalink**) 03-17-2008, 06:12 AM

"Lennart" <Erik.Lennart.Jonsson@gmail.com> wrote in message
news:d23aed7d-34fd-4823-a3b8-7377e7a7508c@e6g2000prf.googlegroups.com...
> 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.

#4 (**permalink**) 03-17-2008, 06:12 AM

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.

#5 (**permalink**) 03-17-2008, 06:12 AM

On Mar 14, 1:15*pm, Ian <ianb...@mobileaudio.com> wrote:
> 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'

#6 (**permalink**) 03-17-2008, 06:12 AM

"Ian" <ianbjor@mobileaudio.com> wrote in message
news:7hzCj.69147$497.45133@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.

#7 (**permalink**) 04-29-2008, 08:26 PM

On Mar 14, 4:06*pm, "Mark A" <nob...@nowhere.com> wrote:
> "Ian" <ianb...@mobileaudio.com> wrote in message
>
> news:7hzCj.69147$497.45133@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?

#8 (**permalink**) 04-29-2008, 08:26 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode