Home / Questions / 143546
Accepted
J. Rodríguez
Asked: 2020-03-06 11:08:39 +0800 CST 2020-03-06 11:08:39 +0800 CST

Convert 'dd/MM/yyyy hh:mm:ss' to 'hh:mm:ss tt', SQL Server

  • 772

Assuming I have the following Date/Time in one tablaof the Database:

Input : 05/03/2018 08:00:00.000 , Output :05/03/2018 18:00:00.000

I need to get the following output:

Entrada: 08:00:00 a.m.

Salida: 06:00:00 p.m.

I have the following:

select case when datepart(hour,entrada) > 12 then convert(varchar(28), cast(entrada as datetime), 8) + ' p.m.'
else convert(varchar(30), cast(entrada as datetime) , 8) + ' a.m.' end as hora_entrada,
case when datepart(hour,salida) > 12 then convert(varchar(28), cast(salida as datetime), 8) + ' p.m.'
else convert(varchar(30), cast(salida as datetime) , 8) + ' a.m.' end as hora_salida,
right(convert(varchar(20),entrada,113),9) as entrada,
right(convert(varchar(20),salida,113),9) as salida
from tablaX

The result obtained is:

enter image description here

As you can see the output is wrong 18:00:00 p.m..

How can I do it without using so many conversions?

Environment: SQL Server 2008 R2

datetime

2 Answers

  1. The only thing you need is to specify the output format, which in this case is a TIME(NO DATETIME, so first you have to convert it to such) and with format 100 (you can see many different examples here ).

    SELECT CONVERT(VARCHAR, CAST(entrada AS TIME), 100) AS hora_entrada,
CONVERT(VARCHAR, CAST(salida AS TIME), 100) AS hora_salida,
right(convert(varchar(20),entrada,113),9) as entrada,
right(convert(varchar(20),salida,113),9) as salida
from tablaX
    • 2
  2. Best Answer

    In case anyone needs it, it ended up like this:

    Solution 1:

    SELECT REPLACE(
          REPLACE(
              STUFF(
                RIGHT('0' + LTRIM(
                  RIGHT(CONVERT(varchar(40),
                    CONVERT(DATETIME, '2018/03/05 18:00:00.000'),109),14)),14),9,4,' '),
             'PM',
             ' p.m.'),
            'AM',
            ' a.m.') as hora_entrada

    Solution 2:

    SELECT  REPLACE(
            REPLACE(
                RIGHT(
                    '0000000000' + 
                        CONVERT(
                        varchar(10), 
                        cast('2018/03/05 18:00:00.000' as time(0)), 
                        109),
                10), 
            'PM', 
            ' p.m.'),
        'AM',
        ' a.m.') as hora_entrada
    • 2