Date in where clause returning 0 rows

Question

I am trying to get data as follows WHERE DateCommande = '2018-09-05' but it doesn't work on my computer.

SELECT * 
  FROM dbo.Commande;

Returns rows, including rows that show 2018-09-05 as the datetime value in a datetime column.

However, on SSMS on my computer, if I add a WHERE clause, the query returns 0 rows:

SELECT *
  FROM dbo.Commande
  WHERE DateTimeColumn = '2018-09-05 00:00:00.000';

Answer 1

yyyy-mm-dd hh:mm:ss.000 is not a language- and regional settings-safe format!

SELECT CONVERT(datetime, '2021-09-07');
SET LANGUAGE Nederlands;
SELECT CONVERT(datetime, '2021-09-07');

Results (db<>fiddle):

-----------------------
2021-09-07 00:00:00.000

-----------------------
2021-07-09 00:00:00.000

Please use yyyy-mm-ddThh:mm:ss.000 - that T is extremely important. So:

WHERE DateCommande = '2018-09-05T00:00:00.000';

Or in your case, probably just something closer to what you have in your question, not what you have in your screenshot:

WHERE DateCommande = '20180905'; -- YYYYMMDD *not* YYYY-MM-DD

But unless you only want those rows without time or are sure there is never going to be a time associated with the date (in which case, why isn't the data type date?), neither of those queries will be safe. Better to say:

WHERE DateCommande >= '20180905'
  AND DateCommande <  '20180906';

See the links in the section entitled Regional formats here, for a lot more background:

• Dating responsibly

Answer 2

Solve it! Thank @Aaron Bertrand!

SET LANGUAGE us_english;
SELECT  *
FROM BdCommerciale2.dbo.Commande
WHERE DateCommande = '2018-09-05';