469,934 Members | 2,699 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

Need help how to do Time format in MySql

hi all,


I have time stored in my table field as hh:mm am or pm (eg 04:15 PM , 02:30 AM). The field is of type varchar.

I need to convert it as a MySql time format.

Can any body help me...Please .. Its urgent
Jun 20 '07 #1
6 3519
r035198x
13,262 8TB
hi all,


I have time stored in my table field as hh:mm am or pm (eg 04:15 PM , 02:30 AM). The field is of type varchar.

I need to convert it as a MySql time format.

Can any body help me...Please .. Its urgent
If you're using MySQL 5 or higher you can use the str_to_date function. Otherwise you'd have to do it outside the database.
Jun 20 '07 #2
pbmods
5,821 Expert 4TB
Here are your options for pre-5 MySQL:
http://dev.mysql.com/doc/refman/4.1/...functions.html

And for MySQL 5:
http://dev.mysql.com/doc/refman/5.0/...functions.html
Jun 22 '07 #3
Hi,

Thanks for your suggestion. I have already seen the link provided by you. But could get idea which funtion and forma to use..

I am using Mysql version 5.1.

Can u tell me the exact function format to get my required result. I need to generate the report based on ascendin order of time.

Since my time field is of varchar type, i am getting the display is String order not in the actual time order what i need.. Thats what i need to convert the varchar format to time format.

Please help me.. My work is pending because of this..

Looking forward for your help at the earliest..




Here are your options for pre-5 MySQL:
http://dev.mysql.com/doc/refman/4.1/...functions.html

And for MySQL 5:
http://dev.mysql.com/doc/refman/5.0/...functions.html
Jun 25 '07 #4
date_format(str_to_date('12:24 am','%h:%i %p'),'%T')
returns 00:24:00

date_format(str_to_date('01:24 pm','%h:%i %p'),'%T')
returns 13:24:00
Jun 25 '07 #5
to just order your result set right, you could use something like this
(even in older mysql versions)

say your time field is named "clock"...

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.           (clock LIKE '%pm%') as t ,  *
  3. FROM   tbl
  4. ORDER BY  clock,t
  5.  
this would just add a virtual column t, containing:
- 0 where clock doesn't contain 'pm' (where clock is am)
- 1 where clock contains 'pm'

so you order your set by clock, and then t
Jun 25 '07 #6
Hi i have used this format function
date_format(str_to_date(starttime,'%h:%i %p'),'%T')

which gave me the required result.


Thanks a lot for your valuable help. Thank you once again.

date_format(str_to_date('12:24 am','%h:%i %p'),'%T')
returns 00:24:00

date_format(str_to_date('01:24 pm','%h:%i %p'),'%T')
returns 13:24:00
Jun 25 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

12 posts views Thread by Rick | last post: by
1 post views Thread by Neal | last post: by
3 posts views Thread by parksch2 | last post: by
2 posts views Thread by phillip.s.powell | last post: by
9 posts views Thread by Paul | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.