Data Manipulation

Question

I wonder if it is possible, after converting the date and bringing only the time, to manipulate this result to return the closed time. The example below details better.

CONVERT(VARCHAR(19), DATEADD(second, cr.open_date ,'1969-12-31 21:00:00'), 121) as 'Data de Abertura',
CONVERT(VARCHAR(2), datepart(hh, DATEADD(second, cr.open_date ,'1969-12-31 21:00:00'))) as 'Hora Abertura',

The above code returns this result:

2018-01-23 08:53:35 ------ 8

2018-01-23 15:59:44 ------ 15

In this case, I would like to take the value and turn to closed date like this: the 8 at 8:00:00, the 15 at 15:00:00. I did not find a way. I’m taking the data, passing to an Excel spreadsheet and creating a macro to perform this procedure.

I also tried something like this:

CONVERT(VARCHAR(8), DATEADD(hour, DATEADD(SECOND, cr.open_date,'1969-12-31 21:00:00'), ''), 114) as 'Data fechada'

However it presents an error: "Argument data type datetime is invalid for argument 2 of dateadd Function."

I wonder if there’s any way to accomplish.

Answer 1

You can use the function DATEADD as follows:

SELECT CONVERT(VARCHAR(8), DATEADD(HOUR, 8, ''), 114);

The above example will result in 08:00:00.

Applying in your query:

CONVERT(VARCHAR(8), DATEADD(HOUR, DATEPART(HH, DATEADD(SECOND, cr.open_date ,'1969-12-31 21:00:00')), 114) AS 'Hora Abertura'