Link to home
Start Free TrialLog in
Avatar of cano63
cano63

asked on

string to time format in db2 quert

How can i convert a string value to a time value in my querys in db2.


something like

select * from table where time = convertTotime('7:00');
ASKER CERTIFIED SOLUTION
Avatar of Member_2_2484401
Member_2_2484401
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cano63
cano63

ASKER

windows 2008 server db2 9.5 express

Can you mimic my example in your DB2?
Avatar of cano63

ASKER

if i use = '7:00' it works

but if i try something like

time between '12:45'  and '1:30'

wont work
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It works beautifully for me:

select *
from   deleteme
where  atime between '19:00:00' and '22:00:00'

ADATE       ATIME   
2011-02-16  19:03:06
2011-02-17  21:03:20

select *
from   deleteme
where  atime between '20:00:00' and '22:00:00'

ADATE       ATIME   
2011-02-17  21:03:20

Open in new window


Remember you have to use military-time and pad out the strings to eight characters (which includes the colons).
Avatar of cano63

ASKER

But the values that i have saved in the db is 1:30 PM not 13:30
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cano63

ASKER

is a varchar column, thats the problem
Avatar of cano63

ASKER

well I change all my columns to time format, now it work.