Solved

string to time format in db2 quert

Posted on 2011-02-16
12
1,391 Views
Last Modified: 2012-05-11
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');
0
Comment
Question by:cano63
12 Comments
 
LVL 18

Accepted Solution

by:
daveslash earned 125 total points
ID: 34912331

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
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 125 total points
ID: 34912389
which platform are you using?
0
 
LVL 1

Author Comment

by:cano63
ID: 34912392
windows 2008 server db2 9.5 express
0
 
LVL 18

Expert Comment

by:daveslash
ID: 34912400

Can you mimic my example in your DB2?
0
 
LVL 1

Author Comment

by:cano63
ID: 34912416
if i use = '7:00' it works

but if i try something like

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

wont work
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 125 total points
ID: 34912520
Did you try like this?

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

Open in new window

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 18

Expert Comment

by:daveslash
ID: 34912589
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
 
LVL 18

Expert Comment

by:daveslash
ID: 34912623

Remember you have to use military-time and pad out the strings to eight characters (which includes the colons).
0
 
LVL 1

Author Comment

by:cano63
ID: 34912729
But the values that i have saved in the db is 1:30 PM not 13:30
0
 
LVL 27

Assisted Solution

by:tliotta
tliotta earned 125 total points
ID: 34912918
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
 
LVL 1

Author Comment

by:cano63
ID: 34913086
is a varchar column, thats the problem
0
 
LVL 1

Author Comment

by:cano63
ID: 34922555
well I change all my columns to time format, now it work.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now