Need help how to do Time format in MySql

[ponvijaya]

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

[r035198x]

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.

[pbmods]

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

[ponvijaya]

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

[tintino]

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

[tintino]

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

SELECT 
          (clock LIKE '%pm%') as t ,  *
FROM   tbl
ORDER BY  clock,t
 

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

[ponvijaya]

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