Does SQL BETWEEN work with string dates?

Question

I have a large SQL which has to generate some dates to use and compare with a DATETIME column that I also convert to a string for the purposes of this to compare strings with strings. But does BETWEEN work with strings?

ie

...
    AND ((pur.StudyYearID <= @StudyYear
        AND CONVERT(varchar, pur.StartDate, 112) BETWEEN CONVERT(varchar, GETDATE(), 112)
                                                     AND CONVERT(varchar, CAST(CAST(YEAR(DATEADD(YEAR, 1, GETDATE())) AS varchar) + '1231' AS DATETIME), 112))
         OR (pur.StudyYearID > @StudyYear
        AND CONVERT(varchar, pur.StartDate, 112) BETWEEN STR(YEAR(GETDATE()) + SUBSTRING(pur.StudyYearID, 2, 1) - SUBSTRING(@Workgroup, 1, 1)) + '0101' 
                                                     AND STR(YEAR(GETDATE()) + SUBSTRING(pur.StudyYearID, 2, 1) - SUBSTRING(@Workgroup, 1, 1)) + '1231'))
    ...

I cannot pin point it, but I "think" this is working. However, I am dubious about the BETWEEN. Does BETWEEN only work with real dates such as DATETIME data types, or can it work like I did above with Strings? And if not, I assume I would have to remove the BETWEEN and replace it with dreaded >= and <=, right?

By the way each date string calculation above does work, as I've tested them individually and I do get dates in the YYYYMMDD format.

Thanks

UPDATE The point of the above was to avoid using >= and <= when comparing strings. I wanted to use BETWEEN, but with DATEs as strings as I wanted to make sure I am comparing "apples with apples". However, it was "poor programming" as I could have simply CAST()ed to strip off the time and used BETWEEN, as mentioned below.

@StudyYearID can be anything from [S1,S2,S3 or S4] @WorkGroup can be anything from '1A,1B,1C,1D,1E up to 4E'

Sorry for the confusion

What are the possible values in `pur.StudyYearID` and `@Workgroup`? — Aaron Bertrand, Dec 05 '13 at 23:06

score 2 · Answer 1 · answered Dec 05 '13 at 22:46

2

Why are you trying to compare dates as strings? Dates should be DATETIME objects. It's not clear what you're doing with StudyYearID or @Workgroup, but if a part of the date needs to be built up some other way, then convert it to a DATETIME object before doing your BETWEEN comparison.

If there is no other way to come up with your "between" values other than building them up as strings first, then at least cast before comparing, like this:

    AND (pur.StartDate BETWEEN GETDATE()
                AND CAST(CAST(YEAR(DATEADD(YEAR, 1, GETDATE())) AS varchar) + '-12-31' AS DATETIME))

... same with your other between values.

answered Dec 05 '13 at 22:46

mayabelle

9,804
9
36
59

I don't have a reference, but you are correct, converting the string dates back to DATETIME would work. However, I am dubious about the BETWEEN when it comes to the time component. I want to strip all time off these dates to compare only dates, and so that's why I used strings. – Fandango68 Dec 05 '13 at 22:50
2

To strip the time, `CAST(pur.StartDate AS date)` – Anon Dec 05 '13 at 22:53
1

If you want to strip off time, there are several methods shown here: http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server The easiest is probably just casting to `DATE` (instead of `DATETIME`) if you're using SQL Server 2008 and up. – mayabelle Dec 05 '13 at 22:53
@Anon You beat me to it by 26 seconds :) – mayabelle Dec 05 '13 at 22:55
How about in SQL Server 2005? – Fandango68 Dec 05 '13 at 22:59
Casting to DATE is fastest - see http://www.sqlperformance.com/2012/09/t-sql-queries/what-is-the-most-efficient-way-to-trim-time-from-datetime. But guess what? Not casting at all is even faster, and will always use an index and *should* have the best shot at proper cardinality estimates. – Aaron Bertrand Dec 05 '13 at 23:01
I agree Aaron, but I have to CAST to convert to a DATE or DATETIME for BETWEEN to work, right? So it seems the answer to my original question is BETWEEN only works with real date datatypes. Thanks – Fandango68 Dec 05 '13 at 23:02
@Fernando68 No, you don't have to cast anything. Please have patience. – Aaron Bertrand Dec 05 '13 at 23:03
?? @AaronBertrand. But that's contrary to the above comments from others. – Fandango68 Dec 05 '13 at 23:09
1

@Fernando68 I mean you don't have to convert the column on the left side of the equation to strip time and try to compare the values as strings. – Aaron Bertrand Dec 05 '13 at 23:16
Ahh. Thanks. Yes I did remove the CONVERT() on the left and restored the BETWEEN and it all works! Thank you everyone. – Fandango68 Dec 05 '13 at 23:17

score 1 · Answer 2 · answered Dec 05 '13 at 22:51

1

Yes, dates converted to a strings in ISO 8601 format will sort in chronological order using any of the collation settings that ship with SQL Server. The question everyone is going to ask you is, "Why in the world are you converting dates to strings before comparing them with each other?"

answered Dec 05 '13 at 22:51

Anon

10,660
1
29
31

As per my comment above. I am "building" a date using values from other columns. How else can you do it then? But of course I should be converting the string date back to DATETIME and then compare as per Mayabelle's comment. – Fandango68 Dec 05 '13 at 22:53

score 1 · Accepted Answer · edited Jul 29 '20 at 18:42

Oh my gosh, so many things going wrong here.

Converting all of these date/datetime values to strings. Don't do it. Date and datetime values are dates and datetime values - converting them to strings causes all kinds of bad things like lack of validation, inability to use indexes for seeks or range scans, dropping of all kinds of in-built date function support, etc.
Converting to varchar without length. This is a bad habit and should be avoided so you aren't a victim to silent truncation.
Using BETWEEN for date range queries. You should only do this if the underlying column is DATE, and even then I'd be careful. In order to properly use underlying indexes (that exist today, or that may exist tomorrow), you should use an open-ended date range.

I think this query is much tidier by avoiding all of the conversions to strings, and also gives you a much better shot at index usage, should an index ever exist on StartDate (today or in the future).

  AND 
  (
    (
      pur.StudyYearID <= @StudyYear
      AND pur.StartDate >= @d AND 
      AND pur.StartDate < DATEADD(YEAR, 1, @next_year)
    )
    OR 
    (
      pur.StudyYearID > @StudyYear
      AND pur.StartDate >= DATEADD(YEAR, YEAR(GETDATE()) 
          + SUBSTRING(pur.StudyYearID, 2, 1) - LEFT(@WorkGroup, 1) - 1900, 0)
      AND pur.StartDate < DATEADD(YEAR, 1 + YEAR(GETDATE()) 
          + SUBSTRING(pur.StudyYearID, 2, 1) - LEFT(@WorkGroup, 1) - 1900, 0)
    )
  )

See! This is what I meant. The BETWEEN is fraught with issues, which is why I gave up and turned everything to strings (strings on the left and right). I did use open-ended date ranges, and I would prefer them actually as it "explains it better" to dummys like me, but to be honest BETWEEN is neater. So why shouldn't I use BETWEEN? Maybe another Stackoverflow question. Thanks anyway — Fandango68, Dec 05 '13 at 23:34
@Fern did you read the link? https://sqlblog.org/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx — Aaron Bertrand, Dec 06 '13 at 00:06
I removed the BETWEEN now after reading your article. Better the devil you know. All good. Thanks again. — Fandango68, Dec 06 '13 at 01:20

Does SQL BETWEEN work with string dates?

3 Answers3