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');
LVL 1
cano63Asked:
Who is Participating?
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.

 
Dave FordSoftware Developer / Database AdministratorCommented:

Greetings!

At least in DB2 for i (a.k.a. AS/400), a time column is stored as a string, and it can be operated on as such.

e.g.

create table deleteme (
  aDate date,
  aTime time
)

<insert a couple rows>

select *             
from   deleteme

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

select *                 
from   deleteme
where  aTime = '21:03:20'

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

HTH,
DaveSlash

Open in new window

0

Experts Exchange Solution brought to you by ConnectWise

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
 
momi_sabagCommented:
which platform are you using?
0
 
cano63Author Commented:
windows 2008 server db2 9.5 express
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Dave FordSoftware Developer / Database AdministratorCommented:

Can you mimic my example in your DB2?
0
 
cano63Author Commented:
if i use = '7:00' it works

but if i try something like

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

wont work
0
 
SharathData EngineerCommented:
Did you try like this?

select * from table where time between convertTotime('12:45') and convertTotime('13:30');

Open in new window

0
 
Dave FordSoftware Developer / Database AdministratorCommented:
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

0
 
Dave FordSoftware Developer / Database AdministratorCommented:

Remember you have to use military-time and pad out the strings to eight characters (which includes the colons).
0
 
cano63Author Commented:
But the values that i have saved in the db is 1:30 PM not 13:30
0
 
tliottaCommented:
But the values that i have saved in the db is 1:30 PM not 13:30

1:30 PM is 13:30 (military time).

But maybe the question has a hidden assumption. Is the column in your table an actual TIME column? Or is it simply a CHAR or VARCHAR column that you want to compare against a time value?

Tom
0
 
cano63Author Commented:
is a varchar column, thats the problem
0
 
cano63Author Commented:
well I change all my columns to time format, now it work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.