DateDiff in SQL ODBC

Question

I'm using a datediff in SQL. It returns records when run directly in sql server 2008, but when I try and run it through ODBC it doesn't bring up an error, but it returns no rows.

SELECT  mc_id, mc_date_entered,
 COUNT([mv_value]) total
FROM MarkbookValue t1
    RIGHT JOIN MarkbookColumn t2 ON t1.mv_column_id = t2.mc_id
WHERE mc_module_id = '703000026609358' 
    AND DateDiff(dd, mc_date_entered, '2012-10-05 20:00:00') = 0
    AND mc_type = 'KEF'
    AND mc_entered_by = 'A.ADMIN'
GROUP BY
    mc_id, mc_date_entered;

Getting rid of the DateDiff lets the function run correctly, but I'd obviously like to have it in there. What am I doing wrong?

It could be your date format that is interpreted differently. Try `'2012-10-05T20:00:00'` instead, — Mikael Eriksson, Oct 08 '12 at 09:44
the 'T' solved it. Fancy adding this as an answer and I'll accept it — pluke, Oct 08 '12 at 09:55

score 3 · Answer 1 · answered Oct 08 '12 at 09:50

3

I would almost always write dates in ISO-8601 format, the one without dashes being YYYYMMDD.
Just would like to also point out that if you want your query to use an index on mc_date_entered and remain SARGABLE, you'll want to rewrite it like this.

SELECT  mc_id, mc_date_entered, COUNT([mv_value]) total
FROM MarkbookValue t1
RIGHT JOIN MarkbookColumn t2 ON t1.mv_column_id = t2.mc_id
WHERE mc_module_id = '703000026609358' 
    AND mc_date_entered >= '20121005'
    AND mc_date_entered <  '20121006'
    AND mc_type = 'KEF'
    AND mc_entered_by = 'A.ADMIN'
GROUP BY
    mc_id, mc_date_entered;

Are you also aware that DATEDIFF(DD only considers the date portion, so there's really no point including the time (if we were still using DATEDIFF)?

answered Oct 08 '12 at 09:50

RichardTheKiwi

105,798
26
196
262

thanks for pointing out the dd issue. I've been switching between dd and minute and dd slipped through. It should really read minute. – pluke Nov 04 '12 at 14:16
If you want the query to perform well, you'll still want an index on `mc_date_entered`, and to change your condition to `AND mc_date_entered >= '2012-10-05 20:00:00' AND mc_date_entered < DATEADD(mm,1,'2012-10-05 20:00:00')` assuming the *datetime* comes from some parameter. – RichardTheKiwi Nov 04 '12 at 20:54
Thanks Richard I'm implementing this, I'd like to accept both answers, but it doesn't allow that unfortunately. No disrespect to your answer but the other is closer to my issue. – pluke Nov 04 '12 at 22:53

score 3 · Accepted Answer · edited May 23 '17 at 12:18

3

YYYY-MM-DD HH:MM:SS is not a safe date format to use for a date time literal value in SQL Server. Depending on SET DATEFORMAT your month and day part might be switched.

YYYY-MM-DDTHH:MM:SS and YYYYMMDD HH:MM:SS are safe to use regardless of SET DATEFORMAT.

To get the rows for a specific date I suggest that you do as in the answer provided by @RichardTheKiwi or if you are in SQL Server 2008 you can cast your column to date to remove the time part.

where cast(mc_date_entered as date) = '2012-10-05'

YYYY-MM-DD is safe for data type date.

edited May 23 '17 at 12:18

Community

1
1

answered Oct 08 '12 at 11:01

Mikael Eriksson

136,425
22
210
281

Didn't know that `YYYY-MM-DD` was safe to use with `date`, thanks. – Andriy M Oct 08 '12 at 11:39
1

@AndriyM - Thanks for the edit. [YYYY-MM-DD HH:MM:SS is also safe for datetime2](http://connect.microsoft.com/SQLServer/feedback/details/290971/deprecate-the-date-literal-interpretation-yyyy-dd-mm-except-in-compatibility-modes-90) – Mikael Eriksson Oct 08 '12 at 11:46

DateDiff in SQL ODBC

2 Answers2