CODEWITHSUNDEEP
sqlsql-servert-sql
Ilya Gazman
Ilya Gazman

Reputation: 32226

Tsql: datediff not working with DEFAULT getdate()

I got a table with date type column and default set to: getdate() But when I try to do datediff() on two separated raws, it always gives me 0 or some constant value. How to assign default value of date and perform datediff() that will work?

Example code, that reproduce my problem

CREATE TABLE Test
(
    id INT PRIMARY KEY NOT NULL IDENTITY,
    date DATE DEFAULT getdate() NOT NULL,
    user VARCHAR(100)
);
CREATE UNIQUE INDEX Test_id_uindex ON Test (id);

Call the below raws with 1 sec delay in between or more

Now look below over the strange outputs of the next queries:

  1. SELECT getdate() as date

Output:

date   
2016-12-22 10:42:40
  1. SELECT * from Test

Output:

id    date          user
1     2016-12-22    Cool    
2     2016-12-22    Cool1

And the last one:

SELECT
  a.id,
  datediff(MILLISECOND, a.date, b.date)
FROM Test a
  JOIN Test b ON a.id = b.id + 1

Output:

id    difTime
2     0

Upvotes: 0

Views: 453

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Your code is fine. You are confusing the date data type with datetime or datetime2. Change your table definition to use a type that stores the time along with the date:

CREATE TABLE Test (
    id INT PRIMARY KEY NOT NULL IDENTITY,
    date DATETIME DEFAULT getdate() NOT NULL,
    user VARCHAR(100)
);

This is a natural place for confusion because GETDATE() returns a value with a time component. However, DATE doesn't support the time component.

You can also use CURRENT_TIMESTAMP, whose name is a bit more evocative of the fact that it returns the time.

Upvotes: 5

Related Questions