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
Solved

string to time format in db2 quert

Posted on 2011-02-16
12
1,394 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
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: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
 
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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
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 …

790 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