SQL BETWEEN是否适用于字符串日期? [英] Does SQL BETWEEN work with string dates?

问题描述

我有一个大型SQL，该SQL必须生成一些要使用的日期并与DATETIME列进行比较，为了将其与字符串进行比较，我还将DATETIME列转换为字符串.但是BETWEEN是否可以使用字符串?

即

...
    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'))
    ...

我无法指出它，但是我认为"这是可行的.但是，我对BETWEEN表示怀疑.是BETWEEN仅适用于实际日期(例如DATETIME数据类型)，还是可以像我上面对Strings那样使用?如果不是这样，我想我将不得不删除BETWEEN并用可怕的> =和< =代替，对吧?

通过上面的每个日期字符串计算的工作方式，因为我已经分别测试了它们，并且确实获得了YYYYMMDD格式的日期.

谢谢

更新 上面的要点是在比较字符串时避免使用> =和< =.我想使用BETWEEN，但是要使用DATE作为字符串，因为我想确保将"apples with apples"进行比较.但是，这是糟糕的编程"，因为我本可以简单地通过CAST()剥离时间并使用BETWEEN，如下所述.

@StudyYearID可以是[S1，S2，S3或S4]中的任何一个 @WorkGroup可以是'1A，1B，1C，1D，1E到4E'之类的任何东西

很抱歉造成混乱

解决方案

噢，天哪，这里出了很多问题.

1. 将所有这些日期/日期时间值转换为字符串.不要这样日期和日期时间值是日期和日期时间值-将它们转换为字符串会导致各种不良情况，例如缺乏验证，无法使用索引进行查找或范围扫描，放弃对各种内置日期函数的支持等./p>

2. 转换为varchar无长度.这是一个坏习惯，应避免使用，这样您就不会成为沉默截断的受害者.

3. 使用BETWEEN进行日期范围查询.仅当基础列为DATE时才应执行此操作，即使这样我也要小心.为了正确使用基础索引(今天存在或明天可能存在)，您应该使用开放式日期范围.

我认为通过避免所有到字符串的转换，该查询要整洁得多，并且如果StartDate上存在索引(今天或将来)，还可以使您更好地使用索引.

   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)
    )
  )
 

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

解决方案

Oh my gosh, so many things going wrong here.

1. 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.

2. Converting to varchar without length. This is a bad habit and should be avoided so you aren't a victim to silent truncation.

3. 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)
    )
  )

这篇关于SQL BETWEEN是否适用于字符串日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！