How to add an hour in timestamp in sql server (without Declare)

sql-server dateadd
chrysa22 picture chrysa22 · Mar 1, 2019 · Viewed 8.2k times · Source

I want to add an hour in the parameter TimeStamp, but not with declare parameter i.e 

DECLARE @datetime2 datetime2 = '2019-03-01T09:25:21.1+01:00'
                SELECT DATEADD(hour,1,@datetime)

I have a column name TimeStamp in a table and i want to add in all data plus 1 hour.

The column

TimeStamp 
2019-03-01T09:25:20.1+01:00
2019-03-01T09:25:21.1+01:00
2019-03-01T09:25:19.1+01:00

I try something like this 

SELECT DATEADD(hour,1, TimeStamp), but i have an error

Conversion failed when converting date and/or time from character string.

Any possible answers ?? Thanks

Answer

Bikram Limbu picture Bikram Limbu · Mar 1, 2019

SELECT DATEADD(hour,1, TimeStamp) is correct

However, The format in TimeStamp is wrong,

So, cast it to DateTime2 First 

CAST(TimeStamp as DateTime2)

OR

CAST('2019-03-01T09:25:20.1+01:00' as DateTime2)

So, 

SELECT DATEADD(hour, 1, CAST(TimeStamp as DateTime2))

Related questions

How to add days to the current date?

I am trying to add days to the current date and it's working fine but when I add 360 days to the current date it gives me wrong value. eg: Current Date is 11/04/2014 And I am adding 360 Days to it, it …

Read More
Subtract minute from DateTime in SQL Server 2005

Suppose I have a datetime field whose value is 2000-01-01 08:30:00 and a duration field whose value is say 00:15 (meaning 15 minutes) If I subtract these two, I should get 2000-01-01 08:15:00 Also if I want to subtract 1:15 (means 1 hour 15 minutes), …

Read More
How to add minutes to the time part of datetime

How to add minutes(INT) to the time part of datetime ? For example : If i have datetime variable like this : @shift_start_time = 2015-11-01 08:00:00.000 @increase = 30 How to get the result : 2015-11-01 08:30:00.000

Read More