Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

string to time format in db2 quert

Posted on 2011-02-16
12
Medium Priority
?
1,399 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 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

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 (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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

610 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