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

Question

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

Are you storing datetimes in a varchar column ? Really ? I mean really ? If that is the case that you made this mistake, than at least store them in a format that is not depending on regional settings, see this http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes The best thing to do however is to alter this table and store the values in the correct format, DateTime2 or DateTime — GuidoG, Mar 01 '19 at 10:00
I couldn't change it to datetime because I call timestamp through web api , which has the format like this 2019-03-01T09:25:19.1+01:00.I try to store it as datetime before post this question but i had the same problem — chrysa22, Mar 01 '19 at 10:20
that mean this is fixed format for your timestamp, do some formatting before storing in your column or take the date part out from this string and apply your condition. — DarkRob, Mar 01 '19 at 10:41
Good news! Although there are many answers the number thereof is still within the sample space of a die throw... :) — J.R., Mar 01 '19 at 20:33
@chrysa22, was your question addressed?. If so, can you please mark it as answered? — Alexander Volok, Mar 23 '19 at 10:44

Bikram Limbu · Answer 1 · 2019-03-01T09:52:56.327

4

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))

edited Mar 01 '19 at 09:52

answered Mar 01 '19 at 09:37

Bikram Limbu

431
6
15

score 1 · Answer 2 · answered Mar 01 '19 at 09:32

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

The error message means that column TimeStamp stored as a string. DATEADD expects a valid value that is date/datetime/datetime2 or can be converted into it from a string. Because a sample value look like DATETIME2, such extra conversion perhaps is needed:

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

score 0 · Answer 3 · answered Mar 01 '19 at 09:31

Your syntax will be fine as defined.

It might be a value in your column that is not able to parse to datetime2 because it contains an invalid character. You could add the ISDATE() to the expression to check if it is valid. https://learn.microsoft.com/en-us/sql/t-sql/functions/isdate-transact-sql?view=sql-server-2017

edit: forgot to mention you could parse before adding with try_cast or try_convert to datetime2

score 0 · Answer 4 · answered Mar 01 '19 at 09:42

0

In Your Timestamp +01:00 represents the Time offset to GMT. You can convert this to your local time and then Add the Hours using DATEADD()

or Remove the Time Offset from the string and add one hour using DATEADD() As suggested by Others.

answered Mar 01 '19 at 09:42

Roshan Nuvvula

803
1
8
28

J.R. · Answer 5 · 2019-03-01T11:13:31.240

0

Maybe you are after this?

 select dateadd(hour,1,convert(datetimeoffset, TimeStamp))

Best to not store dates and times as text though.

Edit: Note that his will retain your time zone information if that is important to you.

edited Mar 01 '19 at 11:13

answered Mar 01 '19 at 10:08

J.R.

1,880
8
16

score 0 · Answer 6 · edited Jun 20 '20 at 09:12

According to this https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#date-and-time-styles you have to convert the timestamp with timezone using type 127.

127 is the input format for:

ISO8601 with time zone Z.

yyyy-mm-ddThh:mi:ss.mmmZ (no spaces) Note: For a milliseconds (mmm) value of 0, the millisecond decimal value will not display. For example, the value '2012-11-07T18:26:20.000 will display as '2012-11-07T18:26:20'.

select convert(datetime2, '2019-03-01T09:25:20.1+01:00', 127)

if you are not using convert and the 127 by using cast you may run in conversion problems depending on language settings of the users.

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

6 Answers6