SeaSide SeaSide - 1 month ago 11
SQL Question

Converting string 'yyyy-mm-dd' to date

I want to select from table where date column is equal to specific date which I sending as a string in format 'yyyy-mm-dd'. I need to convert that string and than to compare if I have that date in my table.

For now I am doing this:

select *
FROM table
where CONVERT(char(10), date_column,126) = convert(char(10), '2016-10-28', 126)


date_column is a date type in table and I need to get it from table in this format 'yyyy-mm-dd' and because that I use 126 format. I am just not sure with the other part where I converting string which is already in that format and do I need to convert it because I don't know is it good to use this:

CONVERT(varchar(10), date_column,126) = '2016-10-28'

Answer

You don't need to convert the column as well. In fact, you better not convert the column, because using functions on columns prevents sql server from using any indexes that might help the query plan on that column. Also, you are converting a string to char(10) - better just convert it to date:

where date_column = convert(date, '2016-10-28', 126)

Also, if you are using a datetime data type and not date, you need to check that the datetime value is between the date you pass to the next date.