RE: SQL time stamp starting beginning of yesterday

If you are wanting to start from the beginning of the day I would use the following:

SELECT *
FROM PROD/tlog
WHERE ttmpstmp >= cast((current date - 1 day) as timestamp)
AND tstep > 2
AND tsystem = 'XXXXXXXX'
GROUP BY tcntnbr

Regards,

Mitch

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Christopher Bipes
Sent: Tuesday, May 09, 2017 12:39 PM
To: 'Midrange Systems Technical Discussion'
Subject: [Caution: Message contains Redirect URL content] RE: SQL time stamp starting beginning of yesterday

SELECT *
FROM authsysf/oeptnslog
WHERE ttmpstmp > timestamp(current_date - 1 day)

SQL State: 42815
Vendor Code: -171
Message: [SQL0171] Argument 1 of function TIMESTAMP not valid. Cause . . . . . :   The data type, length, or value of argument 1 of function TIMESTAMP specified is not valid. Recovery  . . . :   Refer to the DB2 for i5/OS SQL Reference topic collection in the Database category in the i5/OS Information Center for more information on scalar functions. Correct the arguments specified for the function. Try the request again.  Failed statements: 1


Chris Bipes
Director of Information Services
CrossCheck, Inc.

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Tuesday, May 09, 2017 9:33 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: SQL time stamp starting beginning of yesterday

It's the right direction...but on a twisty two lane road...as an index over
ttmpstmp won't be used.

If you want to take the highway...use
ttmpstmp > timestamp(current_date - 1 day)

That will use an index over ttmpstmp...

Charles