locked
to change time part into decimal RRS feed

  • Question

  • here I have the  time part. How can I change into Clockin and Clockout into decimal please help

    ClockIN	ClockOUT
    08:25	16:35               8.25         16.35
    08:05	17:44               8.05         17.44
    09:00	17:00               9.00         17.00
    08:00	13:54
    08:45	17:01
    08:22	17:52
    09:00	17:28
    07:26	18:02
    08:53	17:31
    08:58	16:17

    With Thanks


    polachan

    Tuesday, December 3, 2019 3:34 PM

Answers

  • Declare @Sample Table(ClockIN time, ClockOUT time);
    Insert @Sample(ClockIN, ClockOUT) Values
    ('08:25',	'16:35'),   --            8.25         16.35
    ('08:05',	'17:44'),   --            8.05         17.44
    ('09:00',	'17:00'),   --            9.00         17.00
    ('08:00',	'13:54'),
    ('08:45',	'17:01'),
    ('08:22',	'17:52'),
    ('09:00',	'17:28'),
    ('07:26',	'18:02'),
    ('08:53',	'17:31'),
    ('08:58',	'16:17');
    Select ClockIN, ClockOUT, 
      Replace(Convert(char(5), ClockIN, 114), ':', '.'), 
      Replace(Convert(char(5), ClockOUT, 114), ':', '.')
    From @Sample;
    

    Tom
    • Marked as answer by polachan Tuesday, December 3, 2019 3:55 PM
    Tuesday, December 3, 2019 3:49 PM

All replies

  • Declare @Sample Table(ClockIN time, ClockOUT time);
    Insert @Sample(ClockIN, ClockOUT) Values
    ('08:25',	'16:35'),   --            8.25         16.35
    ('08:05',	'17:44'),   --            8.05         17.44
    ('09:00',	'17:00'),   --            9.00         17.00
    ('08:00',	'13:54'),
    ('08:45',	'17:01'),
    ('08:22',	'17:52'),
    ('09:00',	'17:28'),
    ('07:26',	'18:02'),
    ('08:53',	'17:31'),
    ('08:58',	'16:17');
    Select ClockIN, ClockOUT, 
      Replace(Convert(char(5), ClockIN, 114), ':', '.'), 
      Replace(Convert(char(5), ClockOUT, 114), ':', '.')
    From @Sample;
    

    Tom
    • Marked as answer by polachan Tuesday, December 3, 2019 3:55 PM
    Tuesday, December 3, 2019 3:49 PM
  • You can use format function as well:

    select 
    Format(ClockIN,'hh\.mm') ClockIn,
    Format(ClockOut,'hh\.mm') ClockOut 

    Tuesday, December 3, 2019 4:02 PM