SQL Server date DATEADD function issue

Question

I'm trying to subtract an hour from this date - 2016-08-25 1:19:30.560. I expect and want to get 2016-08-25 12:19:30.560.

Instead I get: 2016-08-25 00:19:30.560

If I then set the date to 2016-08-25 2:19:30.560. I then get 2016-08-25 01:19:30.560. It does it properly.

DECLARE @FakeCurrentDateTime1 datetime
DECLARE @FakeCurrentDateTime2 datetime

SET @FakeCurrentDateTime1 = '2016-08-25 1:19:30.560' 

SET @FakeCurrentDateTime2 = DATEADD(hour, -1, @FakeCurrentDateTime1)

SELECT @FakeCurrentDateTime2

DECLARE @FakeCurrentDateTime3 datetime
DECLARE @FakeCurrentDateTime4 datetime

SET @FakeCurrentDateTime3 = '2016-08-25 2:19:30.560' 
SET @FakeCurrentDateTime4 =  DATEADD(hour, -1, @FakeCurrentDateTime3)

SELECT @FakeCurrentDateTime4

@AdamV . . . You should write and answer and explain the logic. — Gordon Linoff
– Gordon Linoff, Commented Aug 25, 2016 at 20:05

Adam V · Accepted Answer · 2016-08-25 20:06:45Z

3

1:19:30.560 is 1:19 AM in SQL. If you want PM, you need 13:19:30.560 instead, or else you'll need to provide a "PM" like so:

SET @FakeCurrentDateTime1 = '2016-08-25 1:19:30.560 PM'

answered Aug 25, 2016 at 20:06

Adam V

6,3613 gold badges42 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

Or express the time as 13:19:30.560.

Collectives™ on Stack Overflow

SQL Server date DATEADD function issue

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related