Solved

string to time format in db2 quert

Posted on 2011-02-16
12
1,396 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 18

Accepted Solution

by:
Dave Ford 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

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…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

729 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