as 400 date/time query

yes I am. I had the query run without the 'and' conditions for the date and that is what was returned. The table is very small at the moment since we haven't installed to production yet.

Well,

First of all, if I read this right, you are looking for records with a date that is less than or equal to your start time AND simultaneously greater than or equal to your end time.  Unlikely you will find many dates that fit that test.

Second,

A timestamp contains three  components: a DATE, a TIME, and microseconds.

Assuming CPTTST is defined on the AS/400 as a TIMESTAMP, you won't want to sue the date() function at all, since it just strips the date portion (year, month, day) out of a timestamp (year, month, day, hours, minute, second, microsecods)  

If your goal is to use a date  plus a time for your extract, then you just need to compare a properly formatted quoted string to the timestamp field.

- Gary Patterson

Check out my EE profile: http://www.experts-exchange.com/M_4382324.html

'2009-02-02-13.17.23.000000' <= CPTTST

                                              

1:

Also note that in and ISO timestamp, the separator in the time portion of the timestamp is "." (period), not ":" (colon).  I get an error when I try to substitute colons on a native query on the AS/400,

- Gary Patterson

Check out my EE profile: http://www.experts-exchange.com/M_4382324.html

" And date(timestamp('" & NewStart_DateTime & "')) <= date(timestamp(CPTTST)) " & _
" And date(timestamp('" & NewEnd_DateTime & "')) >= date(timestamp(CPTTST)) " & _

states that the start time is less than the database field and the end date is greater than the database field.  

Taking the date portion off of  " And date(timestamp('" & NewEnd_DateTime & "'))" causes an error "SQL0180: Syntax of date, time or datestamp value not valid"

Hi Gary,

Looks like you read it wrong. :)

" And date(timestamp('" & NewStart_DateTime & "')) <= date(timestamp(CPTTST)) " & _
" And date(timestamp('" & NewEnd_DateTime & "')) >= date(timestamp(CPTTST)) " & _


Hi Hi whsrptq,

DB2 has some pretty power date manipulation routines (and syntax). There's no need to jump through all of these hoops when you don't have to. You don't have to build and pass an entire datetime only to recast it to a date. You can build the date and pass it. You can also set the range within the query instead of having to build and pass another date.

" And date('" & NewStart_Date & "') <= date(CPTTST) " & _
" And date('" & NewEnd_Date & "') >= date(CPTTST) " & _

or
" And '" & NewStart_Date & "' <= date(CPTTST) " & _
" And '" & NewEnd_Date & "' >= date(CPTTST) " & _

or even
" And date (CPTST) BETWEEN '" & NewStart_Date & " AND " & NewEnd_Date & "' _


I've probably misplaced a quote, but it's close. :)

Kent

Using the date function causes the same error.  "SQL0180: Syntax of date, time or datestamp value not valid"

GetEmployee_SQL = " SELECT cpttst, cptopc, cptclc " & _
                  " FROM CPETT00 " & _
                  " WHERE cptemp= '" & Neweeempn & "' " & _
                  " And date(timestamp(CPTTST)) BETWEEN '" & NewStart_Date & "' AND '" & NewEnd_Date & "' " & _
                  " ORDER BY CPTTST "

                                              

1:
2:
3:
4:
5:

oops pasted the wrong code

GetEmployee_SQL = " SELECT cpttst, cptopc, cptclc " & _
                  " FROM CPETT00 " & _
                  " WHERE cptemp= '" & Neweeempn & "' " & _
                  " And date(CPTTST) BETWEEN '" & NewStart_Date & "' AND '" & NewEnd_Date & "' " & _
                  " ORDER BY CPTTST "

                                              

1:
2:
3:
4:
5:

Ok.  can you post the value of CPTTST?  

Actual data from database (cpttst):

2009-08-30-21.25.49.000000

2009-08-31-01.30.00.000000

2009-08-31-02.03.00.000000

2009-08-31-05.58.43.000000

Kent,

Yep, read it wrong.  I prefer your BETWEEN syntax: much clearer to me.

whsrptq:

I think that the SQL0180 is because the syntax of your TIMESTAMP constant in the SQL is not correct:

NewStart_DateTime = Format(NewStart_DateTime, "yyyy-MM-dd-hh:mm:ss.0000000")
NewEnd_DateTime = Format(NewEnd_DateTime, "yyyy-MM-dd-hh:mm:ss.0000000")

Swap the ":"s for "."s and see if that doesn't resolve the syntax error.

Do you want your selection criteria to include a date and time, or just a date?  IF so, you need to drop those DATE() references and just compare the TIMESTAMP variable to a valid TIMESTAMP literal.

Again, how is

- Gary Patterson

Check out my EE profile: http://www.experts-exchange.com/M_4382324.html

and CPTTST BETWEEN '2009-02-17-01.02.03.000000' AND '2009-02-18-06.32.17.000000'
 
(Assumes CPTTST is declared as TIMESTAMP (is it?)
                                              

1:
2:
3:

So many things to learn...

So little time.....

HAH!

in typing an updated section to this, my original code was

NewStart_DateTime = Format(NewStart_DateTime, "yyyy-MM-dd-hh:mm:ss.0000000")
2009-08-30-17:01:00.0000000

the as 400 value was  

2009-08-30-21.25.49.000000

the as/400 value has 1 less zero on the end. making the code read

NewStart_DateTime = Format(NewStart_DateTime, "yyyy-MM-dd-hh:mm:ss.000000")

2009-08-30-17:01:00.000000

fixes the issue. OMG i never thought it would be THAT picky.

so the new and correct code is.

NewStart_DateTime = Format(NewStart_DateTime & " " & Left(CODAM1, StartPosition) & ":" & Right(CODAM1, 2) & ":00", "mm/dd/yyyy hh:nn:ss AMPM")
NewStart_DateTime = DateAdd("n", -(CODAM3), NewStart_DateTime)
NewStart_DateTime = Format(NewStart_DateTime, "yyyy-MM-dd-hh.mm.ss.000000")
 
NewEnd_DateTime = Format(CurrentSheet.Cells(4, 2) & " " & Left(CODAM2, StartPosition) & ":" & Right(CODAM2, 2) & ":00", "mm/dd/yyyy hh:nn:ss AMPM")
NewEnd_DateTime = DateAdd("n", CODAM3, NewEnd_DateTime)
NewEnd_DateTime = Format(NewEnd_DateTime, "yyyy-MM-dd-hh.mm.ss.000000")
 
GetEmployee_SQL = " SELECT cpttst, cptopc, cptclc " & _
                  " FROM CPETT00 " & _
                  " WHERE cptemp= '" & Neweeempn & "' " & _
                  " And date(CPTTST) BETWEEN date(timestamp('" & NewStart_DateTime & "')) AND date(timestamp('" & NewEnd_DateTime & "')) " & _
                  " ORDER BY CPTTST "

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

Good job!

- Gary

I feel really, really bad about this.

I've corrected quite a few people that only had 5 digits for the millisecond clock, but completely missed the fact that this code had 7.  :/

Glad that you spotted it!

Kent

no worries. i'm just glad i found it. i was beginning to think i didnt have a clue!

Seems inappropriate to delete the question when we solved:

Misuse of the date() function,
Syntax of the timestamp separators.

Not to mention that I also explained the three components of the timestamp field, including the MICROSECONDS field, which, by definition is 10^-6?

I'm fairly sure that Kent's explanation of how precise you need to be with the timestamp at least assisted you in your solution.

- Gary