Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1402
  • Last Modified:

string to time format in db2 quert

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
cano63
Asked:
cano63
4 Solutions
 
Dave FordSoftware Developer / Database AdministratorCommented:

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
 
momi_sabagCommented:
which platform are you using?
0
 
cano63Author Commented:
windows 2008 server db2 9.5 express
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Dave FordSoftware Developer / Database AdministratorCommented:

Can you mimic my example in your DB2?
0
 
cano63Author Commented:
if i use = '7:00' it works

but if i try something like

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

wont work
0
 
SharathData EngineerCommented:
Did you try like this?

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

Open in new window

0
 
Dave FordSoftware Developer / Database AdministratorCommented:
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
 
Dave FordSoftware Developer / Database AdministratorCommented:

Remember you have to use military-time and pad out the strings to eight characters (which includes the colons).
0
 
cano63Author Commented:
But the values that i have saved in the db is 1:30 PM not 13:30
0
 
tliottaCommented:
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
 
cano63Author Commented:
is a varchar column, thats the problem
0
 
cano63Author Commented:
well I change all my columns to time format, now it work.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now