CODEWITHSUNDEEP
sql-serversql-server-2008
Willy
Willy

Reputation: 10660

SQL Server issues when getting records starting from a date onwards

I want to obtain all records starting from a date (included) onwards.

I get different records if I use below in where clause:

SELECT * FROM MyTable WHERE DateTimeField >= '20101201'

than if I use below:

SELECT * FROM MyTable WHERE DateTimeField >= Convert(datetime, '2010-12-01')

Why don't I get the same number of registers?

I use SQL Server 2008.

Upvotes: 0

Views: 37

Answers (1)

sepupic
sepupic

Reputation: 8697

Your literal constant in the second case is language dependent, here is an example:

set language [British English]
SELECT Convert(datetime, '2010-12-01');
----
-- 2010-01-12 00:00:00.000

set language us_english
SELECT Convert(datetime, '2010-12-01');
----
-- 2010-12-01 00:00:00.000

While '20121201' is language independent.

So I suppose your session language (defined by your login language unless changed explicitely) is different from us_english and to fix the issue you should use language independent date literals 'yyyymmdd'

Upvotes: 3

Related Questions