SQL datetime compare

Question

I want to get some values from my table an there are some conditions about its datetime columns.

I want to get all hotel values of a stated city from my table, which is named "LocalHotels". Also I should declare two DateTimevalues. First value should be less than or equal to hotel's value in "start" column, which is datetime data type. Second value should be greater than or equal to hotel's value in "deadline" column, which is datetime data type, either.

All datetime values in these two columns are inserted in German CultureInfo format.

When I stated query below, there are no problems;

string query = "SELECT * FROM LocalHotels WHERE city='LONDON' AND start <='5.12.2015 00:00:00' AND deadline >='8.12.2015 00:00:00' ORDER BY city";

However when I changed day value of DateTime values from one digit to two digits, as I stated in below;

string query "SELECT * FROM LocalHotels WHERE city='LONDON' AND start <='15.12.2015 00:00:00' AND deadline >='18.12.2015 00:00:00' ORDER BY city"

I got an SQLException which indicates;

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Answer 1

SELECT CONVERT(char(10), GetDate(),126)

or

select convert(varchar,getDate(),112)

or

select replace(convert(varchar, getdate(), 111), '/','-')

Test out the queries above to get the date in the desired format (replace GetDate() with your date, or dateColumn). As others pointed out you need the format YYYY-MM-DD.