date(CMTIMESTMP) >= (current_date - 1 day) ORDER BY CMTIMESTMP
returns the expected result!
... but the Query Optimizer cannot use an index built over the CMTIMESTMP
column.
If possible the "original" column should not be converted on the left side
of the comparison operator.
Also for performance issues calculating the Beginning of yesterday in the
WHERE condition might be not a good idea, calculating the date before and
using a host variable is much better.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan
Shore
Sent: Dienstag, 25. April 2017 22:06
To: midrange-l@xxxxxxxxxxxx
Subject: RE: How to obtain the beginning of yesterday as a time stamp
Oh man
I believe I have it
This seems to work
SELECT CMOLDCST#, CMNEWCST#, CMTIMESTMP FROM cstmerge WHERE
date(CMTIMESTMP) >= (current_date - 1 day) ORDER BY CMTIMESTMP
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
From: Alan Shore
Sent: Tuesday, April 25, 2017 4:01 PM
To: 'midrange-l@xxxxxxxxxxxx' <midrange-l@xxxxxxxxxxxx>
Subject: How to obtain the beginning of yesterday as a time stamp
Hi everyone
I am going to play the sympathy card - so please forgive me Today, I have
just been blasted and nuked from ALL sides and my brain is just mush Before
I forget - we are on V7r1
I have a file with 3 fields of which one is a timestamp Using SQL, how do I
extract ALL the records that have a timestamp equal to or greater than the
beginning of yesterday
For example - today is April 25, 2017
I am looking to extract ALL records with a timestamp of
2017-04-24-00.00.00.000000 and greater, but I do NOT want to hardcode that
value as tomorrow, I want to extract all records with a timestamp of
2017-04-25-00.00.00.000000 or greater and so on Hope that makes sense
As always - all responses are always welcome
Alan Shore
E-mail : ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
