3

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'
SeaSide
  • 149
  • 1
  • 3
  • 15
  • With that format you don't need to convert anything really, it is the default format. ... where date_column = '2016-10-28' would do. It is problematic with some language settings and sql version so a better one would be ... where date_column = '20161028'. Yet the best is not to send a string but a date\datetime from your frontend, drivers do the rest. – Cetin Basoz Nov 02 '16 at 08:28
  • @cetingbasoz If `date_column` also contains a time value, the comparison will never be `true`, as `'2016-10-28'` is `2016-10-28 00:00:00` and unless the time in `date_column` happens to be `00:00:00` they will never be equal. – Thorsten Dittmar Nov 02 '16 at 10:31
  • There is no time in column, just date. – SeaSide Nov 02 '16 at 13:06

3 Answers3

14

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.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • to use like this is not good: where CONVERT(char(10), date_column ,126) = convert(date, '2016-10-28', 126) – SeaSide Nov 02 '16 at 08:20
  • No, this is converting the date_column to a string and the string to a date... you better just convert the string to a date. – Zohar Peled Nov 02 '16 at 08:23
  • @ZoharPeled - Can you explain or compare convert() to cast(date_column as date)? Are there any efficiencies in your method that would not be apparent in the other? – Andrew L Nov 02 '16 at 09:08
  • You can choose the format of the source / target only in `convert`, but not in `cast`. I don't know if one of them is more efficient then the other. – Zohar Peled Nov 02 '16 at 09:11
5

You can convert string to date as follows using the CONVERT() function by giving a specific format of the input parameter

declare @date date
set @date = CONVERT(date, '2016-10-28', 126)
select @date

You can find the possible format parameter values for SQL Convert date function here

Eralper
  • 6,461
  • 2
  • 21
  • 27
2

You do not need to do that. yyyy-MM-dd is the default format.

Please note that you need to take into account the time as well, if there's a timestamp in date_column. In that case you should write something like this

... WHERE date_column >= '2016-10-28 00:00:00' AND date_column < '2016-10-29 00:00:00'

... WHERE date_column BETWEEN '2016-10-28 00:00:00' and '2016-10-29 00:00:00'

As I just learned that (other than I thought) BETWEEN actually includes the end timestamp and thus is not equivalent to the above >= ... < approach.

This should use indexes properly as well.

Thorsten Dittmar
  • 55,956
  • 8
  • 91
  • 139
  • 1
    Prefer `>=` and `<` over `BETWEEN` if you will be querying multiple ranges and wish to avoid double-counting values that occur at exactly midnight. – Damien_The_Unbeliever Nov 02 '16 at 08:06
  • Adding to @Damien_The_Unbeliever 's comment: here's some more detail about his advice: https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries – Jens Nov 02 '16 at 08:34
  • @damien_the_unbeliever Oh - I always thought `between` acted like like `[start...end[`, thus not including the end date? Going to change my answer accordingly. – Thorsten Dittmar Nov 02 '16 at 09:56