Converting Hours format to hh:mm:format in MySql

Question

Currently I'm doing this in SQL:

    select emp_code,in_time,out_time,worked_time,WT,shift_work_time,
    case when enable_overtime = 1 and TIMEDIFF(WT,shift_work_time) > 0
then hour(TIMEDIFF(WT,(shift_work_time))) else 0 end as OT
FROM myTable;

I am getting wrong OT at line 8,because shift_work_time is in hours format,so i want to convert shift_worke_time which is in hours to hh:mm:ss how can i do this? Thank you in advance

I have also tried TIME_FORMAT(shift_worke_time, '%H:%i:%s') but when shift_worke_time is 10 output is 00:00:10 but it should be

10:00:00

check here; https://stackoverflow.com/questions/8338031/mysql-setup-the-format-of-datetime-to-dd-mm-yyyy-hhmmss-when-creating-a-tab — Deepak Dholiyan, Aug 08 '18 at 06:48

Zaynul Abadin Tuhin · Accepted Answer · 2018-08-08T08:51:20.250

0

use STR_TO_DATE(shift_worke_time,'%H')

 SELECT 
  select emp_code,in_time,out_time,worked_time,WT,(shift_worke_time,'%H') as shift_worke_time,
    case when enable_overtime = 1 and TIMEDIFF(WT,(shift_worke_time,'%H')) > 0
then hour(TIMEDIFF(WT,(shift_worke_time,'%H'))) else 0 end as OT
FROM myTable;

edited Aug 08 '18 at 08:51

answered Aug 08 '18 at 06:45

Zaynul Abadin Tuhin

31,407
5
33
63

Thanks for your help,I want to format shift_worke_time which is in if condition and I am getting 00:00 in output – user9862376 Aug 08 '18 at 06:47
then you have to use case when – Zaynul Abadin Tuhin Aug 08 '18 at 06:48
@user9862376 edited my answer by using case when check please – Zaynul Abadin Tuhin Aug 08 '18 at 07:00
it works only when my shift_worke_time is 10 but does not give output when my shift_worke_time is 10:00:00 – user9862376 Aug 08 '18 at 08:11
@user9862376 in that you can use TIME_FORMAT("19:30:10", "%H %i %s"); this formating – Zaynul Abadin Tuhin Aug 08 '18 at 08:43
I have used TIMEDIFF(WT,STR_TO_DATE(shift_worke_time,'%H ') is that correct – user9862376 Aug 08 '18 at 08:48

Converting Hours format to hh:mm:format in MySql

1 Answers1