CODEWITHSUNDEEP
sql-server
user3020047
user3020047

Reputation: 888

SQL Server date DATEADD function issue

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

Upvotes: 0

Views: 77

Answers (1)

Adam V
Adam V

Reputation: 6356

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'

Upvotes: 3

Related Questions