Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Date Comparison

  1. #1
    Join Date
    Jan 2013
    Posts
    1

    Unanswered: Date Comparison

    Other than issues with sorting and MAX, are there any potential issues with using something like CONVERT([varchar](10), datetimefield, 101) which yields a mm/dd/yyyy varchar value.

    That is, the WHERE > and < statement will still work properly as long as it is being compared to a true datetime value?

    The actual syntax would be :

    DECLARE @StartDate as date
    SET @StartDate = some calculated date

    DECLARE @EndDate as date
    SET @EndDate = another calculated date


    CONVERT([varchar](10), MyDatetimeColumn, 101) AS MyDate
    ...
    WHERE MyDate >= @StartDate and MyDate <= @EndDate


    I am not so concerned about efficiency as I am accuracy at this time.

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    I use something very similar to what you have posted with great success with just a couple of differences.

    1st, I use 112 as the argument to the convert function. This returns a date in the yyyymmdd format. This format is easier to sort on and do comparisons with. Though internally to the SQL engine it may not matter. It just makes it easier to "see" what is going when developing these types of queries.

    2nd. When doing the actual comparison, use "Between"

    Code:
    WHERE Convert(varchar, MyDate, 112) between @StartDate and @EndDate
    This clearly states what your intent is. And yes, between is inclusive of the end points. It also removes the time element from the query so that any record that falls on @EndDate, regardless of the time, will be included. This assumes of course that @StartDate and @EndDate have been formatted the same as MyDate!

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are definitely Sargability issues with using any function on a column in the WHERE or the HAVING clauses. For small tables (under 10,000 rows) this isn't likely to cause serious performance problems but as your table grows the performance hit will increase significantly.

    Using BETWEEN is syntactically shorter, and results in exactly the same behavior from SQL Server (you can confirm this by checking the query plan for both syntaxes).

    The short answer boils down to yes, there is a significant impact in converting then comparing the data in a column in the WHERE clause.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Sargability, very interesting. Thank-you for the link. There is so much to learn with SQL.

    I would be interested to know how you would construct a Where clause that use DateTime data types but ignores the time portion of the field to avoid Sargability issues.

    I use the Convert() method in a lot a my queries and if it is going to be a performance bottle neck down the road, I want to fix it sooner rather than later!

    Thanks!

  5. #5
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by LinksUp View Post
    I would be interested to know how you would construct a Where clause that use DateTime data types but ignores the time portion of the field to avoid Sargability issues.
    Use the right function.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by corncrowe View Post
    Use the right function.
    really? after this thread went down the sargability path, showing how functions blow it up real good, you're suggesting a function?

    the solution to "construct a Where clause that use DateTime data types but ignores the time portion" is to use a date range
    Code:
    WHERE MyDateTime >= @StartDate 
      AND MyDateTime  < DATEADD(DAY,1,@EndDate)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by r937 View Post
    really? after this thread went down the sargability path, showing how functions blow it up real good, you're suggesting a function?

    the solution to "construct a Where clause that use DateTime data types but ignores the time portion" is to use a date range
    Code:
    WHERE MyDateTime >= @StartDate 
      AND MyDateTime  < DATEADD(DAY,1,@EndDate)
    So Dateadd isn't a function? I'll have to remember that one.

    P.S. I mentioned right function and not RIGHT() function.
    Last edited by corncrowe; 01-09-13 at 17:18.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by corncrowe View Post
    So Dateadd isn't a function? I'll have to remember that one.
    you will notice that it is ~not~ applied to the column... which is what sargability is all about

    DATEADD(DAY,1,@EndDate) can be computed once, before the execution of the query commences

    i think you're just trying to be clever now
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by r937 View Post
    you will notice that it is ~not~ applied to the column... which is what sargability is all about

    DATEADD(DAY,1,@EndDate) can be computed once, before the execution of the query commences

    i think you're just trying to be clever now
    Good selection.

  10. #10
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by r937 View Post
    the solution to "construct a Where clause that use DateTime data types but ignores the time portion" is to use a date range
    Code:
    WHERE MyDateTime >= @StartDate 
      AND MyDateTime  < DATEADD(DAY,1,@EndDate)
    That tip helped me fix a couple of queries where the Start and End dates were of all type datetime.

    How would you construct the WHERE clause when the user provides the Start and End Date?

    What I have been doing is converting the user provided Start and End Date to a string and passing that to the query. (See earlier post in this thread for an example of this.)

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by LinksUp View Post
    How would you construct the WHERE clause when the user provides the Start and End Date?
    same way, except with actual dates instead of proc parameters
    Code:
    WHERE MyDateTime >= '2012-12-26'
      AND MyDateTime  < DATEADD(DAY,1,'2013-01-13')
    in this case all datetimes within 2013-01-13 are to be included in the results, so once again, we go less than the next date, hence DATEADD 1 day again

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Ahh, DateAdd is very flexible in what dates it will accept.

    Thank-you!

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @d DATETIME
    
    SET @d = GetDate()
    
    SELECT @d
    ,  DateAdd(day, 1, @d) AS plusOne
    ,  DateAdd(day, DateDiff(day, 0, @d), 0) AS today
    ,  DateAdd(day, DateDiff(day, 0, @d), 1) AS tomorrow
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  14. #14
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    We now have DATETIME2(n), DATE and TIME(n) data types along with CURRENT_TIMESTAMP and CAST(), so there is no need to use the old 1970's Sybase/UNIX stuff. We got REAL ANSI SQL and indoor plumbing now, ma! Play with these thigns until you get a feel for it.

    BEGIN
    DECLARE @string_timestamp CHAR(10);
    SET @string_timestamp = '2013-01-25';

    DECLARE @temporal_timestamp DATETIME2(0);
    SET @temporal_timestamp = '2013-01-25 12:01:01';

    SELECT CAST (@string_timestamp AS DATE); -- good
    SELECT CAST (@temporal_timestamp AS DATE); --good

    But the old tricks of casting DATETIME to FLOAT and taking FLOOR() and CEILING(), then casting them back do not work any more. You can do strings:

    CAST ((CAST (@temporal_timestamp AS CHAR(10)) + ' 23:59:59.1' )
    AS DATETIME2(0) )

    But you do not have teh INTERVAL data types yet; keep uign DATEADD().

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...but any of those CASTs will have the sargability issues previously discussed.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •