sql server - T-SQL not understand why gives error when using same datetime format with table in where clause -

can please explain why createddate column has '2017-07-28 21:36:29.120' value, gives error when using in clause same format table has below?

this give error. same data in table.

where createddate <= '2017-07-28 21:36:29.120'

this working. not same format table.

where createddate <= '2017/28/07 21:36:29.120'

the difference date format, ymd in 1 case , ydm in second.

you should never write date literal in culture depending format!

this works on machine, break on customer's machine different culture settings.

find details implicit (language bound) settings here

select * sys.syslanguages;

you can use

set dateformat xyz --details here: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-dateformat-transact-sql

set language xyz --find supported languages statement above

but best - , the recommended! - is:

use culture independant formats odbcor iso8601!

Insert