-1

I have a table with the following columns:

ID -> int
Name -> varchar
Date -> varchar

I want to select values between two dates like this:

SELECT * FROM table WHERE DATE BETWEEN '10-12-2018' AND '11-12-2018';

but this query returns wrong values because I'm comparing to strings.

I already tried to cast the date row to date inside the query but it doesn't work.

I'm using SQL SERVER 2012.

Md. Zakir Hossain
  • 1,082
  • 11
  • 24
Alexandre Cristo
  • 341
  • 3
  • 16
  • 1
    Sample data and expected output would be very helpful! – Prashant Pimpale Dec 17 '18 at 12:31
  • 2
    Use the right datatype for your data, and this isn't a problem. Change your column `[date]` to a `date`; why would you call a column "date" and it be a `varchar`..? – Thom A Dec 17 '18 at 12:33
  • Is not because you're comparing strings, is because the date format is ambiguous. `10-12-2018` could be 12th of October **or** 10th of December, depending on the date format – Remus Rusanu Dec 17 '18 at 12:33
  • The problem is that I cannot change! I already speaked with my boss and it's not possible to change! – Alexandre Cristo Dec 17 '18 at 12:34
  • @Remus it's definately the `varchar` datatype. Did you know that `'12-12-2000'` is "after" `'01-01-2020'`? – Thom A Dec 17 '18 at 12:34
  • 1
    Why can't you change it? What was their reason? The problem is the choice of datatype. – Thom A Dec 17 '18 at 12:35
  • the date we are using it's day-month-year – Alexandre Cristo Dec 17 '18 at 12:35
  • Give example input and output – Killer Queen Dec 17 '18 at 12:35
  • @AlexandreCristo dates have no format. They are binary values, like ints, floats, decimals. Formats apply *ONLY* when you want to parse text into dates or format dates as text for display or export purposes. You just *can't* index the string format you specified. The expression you specified will never work *unless* you parse the string field *and* use proper date literals or parameters. If you parse the field though, you won't be able to use any indexes – Panagiotis Kanavos Dec 17 '18 at 12:48

1 Answers1

3

If you "must" (there is no good reason why you can't) change the data type, you'll have to convert the value of your column. This is going to come at a huge cost, as your query will no longer be SARGable (this means that any indexes you have on [DATE] will not be used).

SELECT *
FROM YourTable
WHERE TRY_CONVERT(date,[DATE],105) BETWEEN '20181210' AND '20181211';
--Note the unambiguous date format for the literal strings

I've used TRY_CONVERT as style 105 assumes all your dates are in the format dd-MM-yyyy, but won't cause an error if there are any that are invalid.

Alternatively, if you can, add a computed column and index it. you can add a persisted date column by doing:

ALTER TABLE YourTable ADD DateDate AS TRY_CONVERT(date,[DATE],105) PERSISTED;

Then (after adding your index(es)) you can query that column instead:

SELECT *
FROM YourTable
WHERE DateDate BETWEEN '20181210' AND '20181211';
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Just a side note - the `Date` data type will also handle correctly the more human-readable readable of the ISO8601 string format - `yyyy-mm-dd`. (The `DateTime` data type will not - [it will still depend on local settings](https://stackoverflow.com/questions/45792766/date-conversion-and-culture-difference-between-date-and-datetime)). – Zohar Peled Dec 17 '18 at 12:52
  • 2
    That's why I always prefer the `yyyyMMdd` format, @ZoharPeled. Regardless of data type and language settings, it is completely unambiguous. – Thom A Dec 17 '18 at 13:02