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.
4 Answers
Even though in Europe and everywhere in the world it makes perfect sense to use the month as the second of three items in the date. In the US and in this case, apparently, SQL's date time is MM.DD.YYYY, so you're going for the 15th month and 18th month
Therefore you should use
string query "SELECT * FROM LocalHotels WHERE city='LONDON' AND start <='12.15.2015 00:00:00' AND deadline >='12.18.2015 00:00:00' ORDER BY city"
or
string query "SELECT * FROM LocalHotels WHERE city='LONDON' AND start <='2015-12-15' AND deadline >='2015-12-18' ORDER BY city"
Try changing
15.12.2015 00:00:00
to
2015-12-15 00:00:00
and same format for the other date also.
You can view SQL Server's date and time format for yourself by running this query:
SELECT
GETDATE()
As you can see the format is YYYY-MM-DD HH:MM:SS.MMM. Stick to this and you won't run into any unexpected conversion errors.
EDIT
ISO 8601 is a standard date format. From MS Docs:
The advantage in using the ISO 8601 format is that it is an international standard with unambiguous specification. Also, this format isn't affected by the SET DATEFORMAT or SET LANGUAGE setting.
For this reason I would recommend it above all other formats. Examples:
2020-02-25
20200225
2020-02-25T18:37:00
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.
