How can I select with a datetime WHERE clause in SQL Server?

Question

If I need to select just less than datetime, I do something like this:

  SELECT StackerReplacedEventID, Occured 
  FROM StackerReplacedEvent 
  WHERE Occured<CONVERT(DATETIME, 'Jul 11 2011 03:50PM', 120)

but I get an error. I've also tried this way:

WHERE Occured<'Jul 11 2011 03:50PM'

Answer 1

Use an ISO date format instead of a regional and/or language-specific one.

WHERE Occured < '2011-07-11T15:50:00';

Now that we know the language settings are Russian (after 15 tries), maybe we can do some brute force work here.

WHERE Occured < CONVERT(DATETIME, 
    REPLACE(N'Jul 11 2011 03:50PM', N'Jul', N'июл'), 100)

Note that the N prefixes are important!

So in reality since you will have data representing all 12 months, it will probably be something like:

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
original_column,N'Jan', N'янв')
,N'Feb',    N'фев')
,N'Mar',    N'мар')
,N'Apr',    N'апр')
,N'May',    N'май')
,N'Jun',    N'июн')
,N'Jul',    N'июл')
,N'Aug',    N'авг')
,N'Sep',    N'сен')
,N'Oct',    N'окт')
,N'Nov',    N'ноя')
,N'Dec',    N'дек')

This is awful, though. Please consider choosing the right data type for this data.

Now do you see why understanding what language you were using, instead of "helpfully" translating the error message, was important?

Answer 2

CONVERT(DATETIME, 'Jul 11 2011 03:50PM') it's working for me