T-SQL Remove Time From DateTime

Question

What is the difference between these 2 lines in SQL Server 2005 Express?

DATEADD(d, 0, DATEDIFF(d, 0, @Today));

and

DATEADD(d, DATEDIFF(d, 0, @Today), 0);

Other than making this statement fail at random times:

DECLARE @DateSrc DATETIME;

-- Chop off the time part:
SET @DateSrc = DATEADD(d, 0, DATEDIFF(d, 0, @Today));

INSERT INTO dbo.SeqNo(MyGUID, TheDay, LastNo)
SELECT @MyGUID, @DateSrc, 0
WHERE NOT EXISTS ( 
  SELECT 1 FROM dbo.SeqNo AS sn
  WHERE sn.MyGUID = @MyGUID AND sn.TheDay = @DateSrc 
  );

Did you read the [`DATEADD`](http://msdn.microsoft.com/en-gb/library/ms186819.aspx) documentation? What did you not understand? — Oded, Feb 08 '13 at 21:39
@Oded - That says `DATEADD (datepart , number , date )` the OP is doing `DATEADD (datepart , number , number )` so the answer to this question is whether it ever makes any difference which number is implicitly cast (I don't think it does) — Martin Smith, Feb 08 '13 at 21:40
@Oded I did and the first one looks correct, but apparently the second one is. — Cheval, Feb 08 '13 at 21:43
@Cheval - As far as I can see they both return the same thing. Have you an example value for `@Today` where they don't? — Martin Smith, Feb 08 '13 at 21:51
You can also SET @DateSrc = convet(varchar(10),@Today,101) which will give the same result — Lance, Feb 08 '13 at 22:29
@Lance no, don't convert to a string. http://www.sqlperformance.com/2012/09/t-sql-queries/what-is-the-most-efficient-way-to-trim-time-from-datetime http://www.sqlperformance.com/2012/10/t-sql-queries/trim-time — Aaron Bertrand, Feb 09 '13 at 05:30
I would much, much, much prefer `DATEADD(DAY,` over `DATEADD(d,`. Why not be explicit? Not being explicit leads to confusion when you have things like n, ns, m, mi, mm, mcs, ms, w, wk, ww, y. Some of those don't mean what you might think they mean. — Aaron Bertrand, Feb 09 '13 at 05:32
Also, when you finally upgrade from SQL Server 2005 (if you are using Express, why aren't you using a newer edition), you can run this query without worrying about all this clunky "chopping off the time part"... — Aaron Bertrand, Feb 09 '13 at 05:34
@MartinSmith yes, I've done the tests and over a million different DateTimes of Today they both are equal, but what if the Today is set from external to SQL Server? I'm wondering if it's not bit perfect, the DateDiff of the first one doesn't create a clean Date only value, which passes the where clause but fails on the constraint. — Cheval, Feb 09 '13 at 14:29
@AaronBertrand Yes, fully explicit is better, but that doesn't make it fail right? Somehow, at random times, the insert creates a "Violation of UNIQUE KEY constraint" on MyGUID and TheDay. [See this link](http://stackoverflow.com/questions/14743785/sql-where-exists-doesnt-filter) — Cheval, Feb 09 '13 at 14:33
Wasn't my point. Anyway I suspect the problem with the violation is that you're passing the same GUID twice, nothing to do with the date. — Aaron Bertrand, Feb 09 '13 at 19:52
@MartinSmith There is no implicit conversion in the second version. The zeros is changed to a constant `datetime` value already in the query plan. The first has an implicit conversion on the value returned by `datediff`. — Mikael Eriksson, Feb 10 '13 at 07:24
@MikaelEriksson - Well `0` is still implicitly converted to `1900-01-01` it just happens during compilation (constant folding) rather than at execution time. — Martin Smith, Feb 10 '13 at 11:49
@AaronBertrand I'm not following you? The point of the WHERE NOT EXISTS clause is that duplicates will never be a problem. If same GUID and date, then nothing happens, if either guid or date is different, then insert. Do you read the SQL differently? — Cheval, Feb 10 '13 at 23:31
@MartinSmith Just to be pedantic, it converts to 1753-01-01 not 1900-01-01 due to calender change overs. — Cheval, Feb 10 '13 at 23:39
@Cheval I guess I'm confused. You're saying you randomly get a unique constraint violation, right? If so, then I'm saying, you're sitting here worrying about the date causing the problem, and I don't think that's the problem. — Aaron Bertrand, Feb 11 '13 at 01:17
@AaronBertrand Sorry, but I'm looking for the side effects which would most likely be the cause. The GUID is always the same for the location. It's never mutated. The date on the other hand, is mutated and from all appearances, was done so via an incorrect usage of some t-sql functions. Also that where WHERE clause is using the equal operator, so it must be exactly the same, where as with the DATETIME type, you usually use the >= and < operators which allows for ever so slight variation in the data. Otherwise it's a bug in SQL which I would have thought would have been picked up on by now. — Cheval, Feb 11 '13 at 06:22
@AaronBertrand so the bottom line is; is the SQL statement wrong, the data wrong or SQL Server wrong? — Cheval, Feb 11 '13 at 06:24
@Cheval - You're wrong. Casting `0` to `datetime` gives `1900-01-01`. Casting `-53690` would give you 1753. — Martin Smith, Feb 11 '13 at 08:32
@Cheval it's not a bug in SQL Server; sorry to burst your bubble. — Aaron Bertrand, Feb 11 '13 at 11:59
@Cheval Your two ways of removing the time part will not create different `datetime` values. And even if it did create different values you would not see any unique key constraint errors since the values are different and would be allowed. You are most likely experiencing a concurrency issue. — Mikael Eriksson, Feb 11 '13 at 13:19
@MikaelEriksson I initially also thought a concurrency issue, so in a prior iteration, I wrapped the statement in a transaction. Not fixed. Also I'm to understand that INSERT INTO WHERE NOT EXISTS creates an implicit transaction, is that not true? — Cheval, Feb 11 '13 at 23:35
@AaronBertrand Sorry if I caused offense, but I'm not looking for any bubbles, just a solution to a very perplexing bug. — Cheval, Feb 11 '13 at 23:36
@MartinSmith Yes you're right; 1900-01-01. Apparently I needed to check more than BOL and a few web references. — Cheval, Feb 11 '13 at 23:41
@MikaelEriksson I just created a test environment that forced a concurrency problem where I created a large date range and for connection #1 add 1 day, con #2 2 days, con #3 3 days. Started #1, then #2 then #3. Result? No error and correct 1 insert & 0 insert messages. So I'm still at a loss. — Cheval, Feb 11 '13 at 23:58
@Cheval - RE: "INSERT INTO WHERE NOT EXISTS" see http://stackoverflow.com/q/3407857 — Martin Smith, Feb 12 '13 at 08:44
@MartinSmith in truth, I'm still at a loss in how I can't reproduce the problem within management studio though. Oh well, hopefully all fixed. — Cheval, Feb 15 '13 at 06:18

score 0 · Answer 1 · answered Feb 09 '13 at 01:16

0

They will both produce the same result but the second is the better format because it will work for other intervals (hour, month, year etc) and the first won't.

answered Feb 09 '13 at 01:16

Dale M

2,453
1
13
21

ok, but I think the bug is more subtle. The second resolves the 0 in the DateAdd to real Date only value and then adds the number of days to it to get a clean Date only value. Where as the first calcs the Date from number of days, but I don't think it's a pure Date only value. – Cheval Feb 09 '13 at 14:25
There is no implicit conversion from int to datetime so the function must be doing a conversion explicitly itself. Since the dateadd function with last parameter int is not documented, you leave open the risk that future revisions may not support what you are doing. – Dale M Feb 09 '13 at 20:27
@DaleM = There **is** an implicit conversion from `int` to `datetime`. [See chart here](http://www.microsoft.com/en-gb/download/details.aspx?id=35834) – Martin Smith Feb 10 '13 at 12:02

score 0 · Answer 2 · answered Feb 09 '13 at 11:28

0

The syntax is: DATEADD (datepart , number , date )

Line 1: DATEADD(d, 0, DATEDIFF(d, 0, @Today));

Line 2: DATEADD(d, DATEDIFF(d, 0, @Today), 0);

So the answer is that number resolves to an int (line 2) , and date resolves to a datetime (line1) - and that is the difference.

answered Feb 09 '13 at 11:28

Bingo

64
4

Yeah, I thought along that line as well but both return the Date only part of the Today DATETIME variable, so that you can compare the result of both many times with different Today values and they match. – Cheval Feb 09 '13 at 14:19
I would just stick to the correct way, even the other also works in this particular code – Bingo Feb 10 '13 at 06:08
actually the DATEADD third parameter of zero gets converted to 1753-01-01 which I think is the key to the problem. It starts with a valid DATETIME and adds to it. Where as in Line 1 the DATEDIFF might not always create a valid DATETIME and then adds zero days to it. – Cheval Feb 10 '13 at 23:42

Cheval · Accepted Answer · 2013-02-15T06:21:35.843

0

The problem was a concurrency issue.

The change of the stored procedure has been in production now for a few days and no problems; interesting fix one thinks. Apparently both versions work.

But, I just reviewed the code again that checks for a single instance of the application and it was moved to after this stored procedure call. Arhhgg! Concurrency issue.

Sorry for the trouble and thanks again for your help.

ps. Just one interesting thing... why couldn't I reproduce the concurrency problem within SQL Server Management Studio as per my comment above? That interleaved perfectly with the newer DateAdd format.

edited Feb 15 '13 at 06:21

answered Feb 15 '13 at 06:15

Cheval

403
4
14

I think it is because you are adding different number of days in each thread. Just did a quick test with two threads and if they both increment by 1 I get a constraint error but if the second thread increments by 2 it is more unlikely to happen. [Here is the code I used](http://pastebin.com/iGuHNkAY) – Mikael Eriksson Feb 15 '13 at 06:42

T-SQL Remove Time From DateTime

3 Answers3