Date manipulation

Navigation
4

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

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 take the value and turn it into such a closed date: the 8 at 8:00 p.m., the 3 p.m. at 15:00 p.m. I could not find a way. I'm getting the data by going to an Excel worksheet and creating a macro to do this.

I've 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 throws an error: "Argument data type datetime is invalid for argument 2 of dateadd function."

I wonder if there is any way to do it.

    
asked by anonymous 27.04.2018 / 20:32

1 answer

1

You can use the DATEADD function as follows: 

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

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

Applying to your query : 

CONVERT(VARCHAR(8), DATEADD(HOUR, DATEPART(HH, DATEADD(SECOND, cr.open_date ,'1969-12-31 21:00:00')), 114) AS 'Hora Abertura'
    
27.04.2018 / 20:39
Performs function x minutes after submitting form Spring boot Data-JPA and JSF Java Config