How do I convert a value after using DATEADD with it

Question

I have a little query that strips the date from the datetime field but when I try to convert it from GMT to CST it readds the date. Is there a better way to do this?

Location table:

arrival
4-6-2018 12:35:43




SELECT arrival
FROM(
SELECT CONVERT(VARCHAR(8), arrival))
FROM locations
)a

This query will give me this result:

12:35:43

SELECT (DATEADD(hour,-5,arrival))
FROM(
SELECT CONVERT(VARCHAR(8), arrival))
FROM locations
)a

4-6-2018 12:35:43

This query will give readd the date. How can I remove the date and then do the dateadd function without it readding the date

Answer 1

arrival seems to be a DateTime, which always carries a date part. You need a time instead, supported by SQL Server 2008+:

cast(DATEADD(hour,-5,arrival) as time)

Answer 2

To quote from DATEADD (Transact-SQL) - Return Types:

The return data type is the data type of the date argument, except for string literals. The return data type for a string literal is datetime. An error will be raised if the string literal seconds scale is more than three positions (.nnn) or contains the time zone offset part.

Emphasis my own.

As you are passing a string (varchar), then DATEADD is returning a datetime.

Like those in the comments have said, if you use the correct data type (time) this problem goes away:

SELECT DATEADD(HOUR, -5,CONVERT(time,Arrival))
FROM (VALUES('4-6-2018 12:35:43'),('4-6-2018 07:35:43'),('4-6-2018 03:35:43')) V(Arrival)