CODEWITHSUNDEEP
sqlsql-serverdatetimesql-server-2012
i.n.n.m
i.n.n.m

Reputation: 3046

DATEDIFF does not return an integer value T-SQL

I am trying to see if how much time it took to insert some values into a table in SQL Server 2012.

This how I am trying to do:

DECLARE @starttime DATETIME
DECLARE @timetook DATETIME

CREATE TABLE MyTable (id INT, name CHAR(10));

SET @starttime = SYSDATETIME()

INSERT INTO MyTable (id, name) 
VALUES (1, 'Bob'), (2, 'Peter'), (3, 'Joe'),
       (4, 'Boby'), (5, 'Peters'), (6, 'Joey'),
       (7, 'Bobs'), (8, 'Petery'), (9, 'Joes');

SET @timetook = DATEDIFF(ss, @starttime, SYSDATETIME())

SELECT @timetook

This returns 1900-01-01 00:00:00.000. According to the datediff docs, it should be 00:00:00.000 or an integer value. Since I am taking the seconds, I should get the number in seconds. (For this example it should be 0 but without 1900-01-01)

What am I missing here? Any suggestion would be appreciated.

Upvotes: 1

Views: 317

Answers (1)

Jacob H
Jacob H

Reputation: 2515

Declaring @timetook as datetime will give you a datetimeresult. And the datetime = 0 is specifically 1900-01-01 00:00:00.000.

declare @starttime datetime
declare @timetook int --Issue was this declaration as datetime

CREATE TABLE MyTable (id int, name char(10));

set @starttime = SYSDATETIME()

INSERT INTO MyTable (id, name) VALUES (1, 'Bob'), (2, 'Peter'), (3, 'Joe'),
                                      (4, 'Boby'), (5, 'Peters'), (6, 'Joey'),
                                      (7, 'Bobs'), (8, 'Petery'), (9, 'Joes');
set @timetook = DATEDIFF(ss, @starttime, SYSDATETIME())

select @timetook

By declaring @timetook as int, you will get the result you are looking for: 0

Upvotes: 3

Related Questions