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

Question

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

Navigation

#1 by (2 votes)
#2 by (2 votes)

2

Assuming I have the following Date / Time in a tabla of the Database:

Entry : 05/03/2018 08:00:00.000 , Exit : 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:

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 sql-server-2008-r2

asked by J. Rodríguez 05.03.2018 в 20:08

source

2 answers

2

The only thing you need is to specify the output format, which for this case is a TIME (NO DATETIME , therefore first you have to convert it to that) and 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

answered by 05.03.2018 в 20:58

how to put the information of my page on image? [closed] get distance based on latitude and longitude

score 2 · Accepted Answer

In case someone needs it, in the end it has stayed 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