db2 timestamp

dear experts

I have column in my table CUSTOMER with column 'UPDATED' which contains data like

9/23/2015 8:28:59.204799 AM
9/23/2015 8:28:59.280789 AM
9/23/2015 8:43:25.738472 AM
9/23/2015 8:43:25.764559 AM
9/23/2015 8:54:17.638960 AM
9/23/2015 8:54:17.677960 AM
9/23/2015 8:54:51.907339 AM
9/23/2015 8:54:51.907456 AM
9/23/2015 9:33:59.198716 AM
9/23/2015 9:39:41.513715 AM
9/23/2015 10:36:53.743423 AM
9/23/2015 1:09:44.151856 PM
9/23/2015 1:15:19.834045 PM
9/23/2015 1:58:08.405602 PM
9/23/2015 4:01:38.369661 PM



Am trying to get data after 1pm on 9/23/2015

select * from CUSTOMER where UPDATED > '2015-9-23 1:00:00.000000'

but it brings data after 1AM and not 1PM.


Any ideas?
royjaydAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi Roy,

DB2 has all kinds of date and time functions that aren't in other engines.  :)

Use the HOURS () function.  To get all of the rows that were recorded during the afternoon this calendar year, use a filter like this:

WHERE date(YourTimeStamp) >= '01/01/2015'
  AND hours (YourTimeStamp) >= 12


Good Luck!
Kent
royjaydAuthor Commented:
Thanks
   Does this look ok?
 
 select * from CUSTOMER where date(UPDATED) > '2015-9-23' and hours(UPDATED) >= 13

Open in new window

 
13 represents 1pm.
   
   I am using Db2 9.1 version and it complains
   No authorized routine named HOURS of type funciton having compatible arguments was found.
Kent OlsenDBACommented:
Sorry.  fat fingers here...

Use HOUR, not HOURS.

select * from CUSTOMER where date(UPDATED) > '2015-9-23' and hour(UPDATED) >= 13

If you want all updates after that time, you'll need to accept all values after that date and the afternoon values for the start date.

select *
from CUSTOMER
where date(UPDATED) > '2015-9-23'
   or (date (updated) = '2015-9-23' and hour(UPDATED) >= 13)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dave FordSoftware Developer / Database AdministratorCommented:
This would be simpler:

select * from CUSTOMER where UPDATED > '2015-09-23-13.00.00.000000'
royjaydAuthor Commented:
thanks all
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.