Improve company productivity with a Business Account.Sign Up

x
?
Solved

string to time format in db2 quert

Posted on 2011-02-16
12
Medium Priority
?
1,405 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:
Dave Ford earned 500 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 500 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 18

Expert Comment

by:Dave Ford
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 41

Assisted Solution

by:Sharath
Sharath earned 500 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
 
LVL 18

Expert Comment

by:Dave Ford
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:Dave Ford
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 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

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…
To export Lotus Notes to Outlook PST or Exchange and Domino Server files to Exchange Server or PST files with ease, go for Kernel for Lotus Notes to Outlook conversion tool. Through the video, you can watch the conversion process. A common user with…
Watch the video to know the process of migration of Exchange or Office 365 mailboxes in absence of MS Outlook. It is an eminent tool which can easily migrate Public, Archive user mailboxes from one another Exchange server and Office 365. Kernel Migr…

607 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