locked
Best Practice filtering a Datetime Data Column by a provided StartDate and EndDate DATE Parameters RRS feed

  • Question

  • So I'm sure this has been beaten to death...I think even I myself have beaten this to death.

    We are wondering what the SQL Server Best Practice is when it comes to creating a SQL Server Stored Procedure that has to filter by a Datetime data column type based on a @StartDate and @EndDate DATE Parameters?

    It makes total sense from both a Reporting and ETL perspective to provide the @StartDate and @EndDate as DATE data types since usually that is the most common way to filter and ask for the data.

    Now...we can most certainly CONVERT the @StartDate and @EndDate to Datetime data formats to resolve any implicit conversion. And I know from past experience that you shouldn't "BETWEEN" the Dates.

    I guess counter intuitively we could also CONVERT the Database Datetime data columns to DATE.

    But like I said...we are wondering what the best practice is for doing this.

    Thanks for your review and am hopeful for a reply.

    Friday, August 14, 2020 8:24 PM

All replies

  • The most recommended way is to use >=@StartDate and < @EndDate where @EndDate is the 12am of the next day you need.

    Tibor K has a good blog post on the topic or alternatively Aaron Bertrand has a good blog post 'Bad habits to kick: mishandling date queries'.


    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, August 14, 2020 9:05 PM
  • Actually found this YouTube which discusses this point. Just not sure if this is "Best Practice" or not...

    https://www.youtube.com/watch?v=A7E3EdZpPOU

    Friday, August 14, 2020 9:10 PM
  • The @StartDate and @EndDate Parameters will definitely be a DATE column data type though.
    Friday, August 14, 2020 9:11 PM
  • I'm no expert but I would think that the less specific DATE type would be better than the more specific DATETIME type in most cases, especially with regard to your end date. A DATETIME of 2020-08-14 16:04:57.797 would fail a <= @EndDate test if @EndDate = 2020-08-14 which would implicitly convert to DATETIME as 2020-08-14 00:00:00.000.

    What makes you say you shouldn't use BETWEEN with dates? 

    Friday, August 14, 2020 9:12 PM
  • Try to find Aaron's blog (old one, but a good one and a bit funny)

    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Friday, August 14, 2020 9:15 PM
    Friday, August 14, 2020 9:13 PM
  • I did not feel like watching the video, but I vote with Naomi:

    WHERE datetimecol >= @StartDate
      AND datetimecol < dateadd(DAY, 1, @EndDate)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, August 14, 2020 9:55 PM
  • Naomi did not provide a link unfortunately.

    But I think I found it...

    https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries

    Are you both implying just to rely on SQL Server implicit conversion of the DATETIME column...or the @StartDate DATE Variable?

    The YouTube is really short...but informative and kind of explains implicit conversion that I thought should be avoided. 

    I think I'm going to go with the YouTube to CONVERT(DATE, [DateTimeStampDataColumn]) >= @StartDate

    That seems like the cleanest and most efficient to me.

    Saturday, August 15, 2020 9:59 AM
  • Use datetime as the data type for the input parameters to the stored procedure - and reset any values passed into the procedure to be midnight:

    CREATE PROCEDURE dbo.SomeProcedure
           @startDate datetime
         , @endDate datetime
        AS
    
       SET NOCOUNT ON;
    
     --==== Method 1
       SET @startDate = dateadd(day, datediff(day, 0, @startDate), 0);
       SET @endDate = dateadd(day, datediff(day, 0, @startDate), 0);
    
     --==== Method 2
       SET @startDate = cast(@startDate As date);
       SET @endDate = cast(@endDate As date);
     

    The second method will have an implicit conversion - but that is okay since it isn't actually part of the query and won't cause any issues with the execution.  The goal here is to make sure your input parameters do not have some time component that will cause inconsistent results based on differing times.

    As for using the parameters - you want to use a half-open interval range for datetime data types.  This way you don't have to worry about the precision of the datetime datatype.

    WHERE [DateColumn] >= @startDate
    AND [DateColumn] <  dateadd(day, 1, @endDate)

    By using less than and the 'next' day at midnight you include everything up to but not including the next day at midnight regardless of the time component on the [DateColumn].  If the [DateColumn] is changed to a datetime2 data type - then all you need to change is the input parameters to match the column and the rest of the code will work exactly the same.


    Jeff Williams

    Saturday, August 15, 2020 4:21 PM
  • I'm no expert but I would think that the less specific DATE type would be better than the more specific DATETIME type in most cases, especially with regard to your end date. A DATETIME of 2020-08-14 16:04:57.797 would fail a <= @EndDate test if @EndDate = 2020-08-14 which would implicitly convert to DATETIME as 2020-08-14 00:00:00.000.

    What makes you say you shouldn't use BETWEEN with dates? 

    For datetime data types - using BETWEEN will cause issues with the time portion and you will either include rows not intended or exclude rows you want.

    For example - assume our [DateColumn] is a datetime data type, @startDate is a date data type = '2020-01-01' and @endDate is a date data type = '2020-07-31':

    WHERE [DateColumn] BETWEEN @startDate AND @endDate

    This is expanded to:

    WHERE [DateColumn] BETWEEN '2020-01-01 00:00:00.000' AND '2020-07-31 00:00:00.000'

    In this scenario - all values in [DateColumn] that fall after midnight on 07-31 will be excluded, which isn't what you want.

    Changing it to:

    WHERE [DateColumn] BETWEEN @startDate AND dateadd(day, 1, @endDate)

    This is expanded to:

    WHERE [DateColumn] BETWEEN '2020-01-01 00:00:00.000' AND '2020-08-01 00:00:00.000'

    And that will include rows from the next month that occurred right at midnight.

    The next option is to then try to manipulate the @endDate parameter to include the maximum end time - but if you then change from datetime to datetime2 that has a higher precision you would still have problems and additional changes needed to your code.

    Much easier to use the half-open interval checking...it is safer and easier to manage and maintain.


    Jeff Williams

    • Proposed as answer by Naomi N Sunday, August 16, 2020 2:53 AM
    Saturday, August 15, 2020 4:31 PM
  • I didn't provide the link but I provided enough information for you to find it and looks like you did find it yourself. If you read it, you'll see it essentially first shows all incorrect ways people may use and then shows the correct way which is what Jeff explained in his message too. 

    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, August 16, 2020 2:56 AM
  • Hi ITBobbyP,

    Agreed with other experts, you could always use an open-ended date range and never use between.

    Below is better:

    WHERE DateTimeStampDataColumn >= @StartDate
    AND DateTimeStampDataColumn < dateadd(DAY, 1, @EndDate)

    Best regards

    Melissa



    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Monday, August 17, 2020 5:33 AM
  • So if y'all will allow me to ask this in a different way...

    So in a SSIS Package, we make use of two variables GlobalStartDate and GlobalEndDate. The variables are both defined as DateTime because that's what's available on the drop-down variable list. 

    The SQL Server Stored Procedure is defined with variables @StartDate and @EndDate. And they are defined as DATE data types within the SQL Server Stored Procedure.

    So I believe what everyone is saying here is rather than using a DATE data type in the SQL Server Stored Procedure and SQL Server Stored Procedure variables @StartDate and @EndDate to use DATETIME data type instead.

    So let me take this one step forward. We do utilize a DateOverride SSIS Variable as well which allows us to Override the derived GlobalStartDate and GlobalEndDate which will become @StartDate and @EndDate. Even though the variables GlobalStartDate and GlobalEndDate are defined as DateTime we are actually providing MM/DD/YYYY, I.E. 01/01/2020, within the Variable in the SQL Server Job Variables. Is that all ok? And then handle the derivation as Jeff suggested?

    Thanks soooo much for your patience and help with this. We REALLY appreciate it!

    Monday, August 17, 2020 1:45 PM
  • The fact that the start and end dates are defined as DATE type in the stored procedure doesn't really matter. They can be defined as date and it's fine. They will implicitly be converted to datetime when performing the actual query. The main point is to just use >= and < for the query itself and increment the end date by one day.

    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, August 17, 2020 1:49 PM
  • Thank You Naomi!

    And Thank You for your patience with me as well.

    I GREATLY appreciate it.

    I think I've got it now based on everyone's input and have taken some good notes and added it to My SQL Server Knowledgebase.

    Thanks Again!

    Monday, August 17, 2020 9:35 PM
  • So I believe what everyone is saying here is rather than using a DATE data type in the SQL Server Stored Procedure and SQL Server Stored Procedure variables*@StartDate* and *@EndDate* to use DATETIME data type instead.

    No, not everyone. I don't say it. I think using DATE is fine for the parameters, even if the columns are datetime. You should only use datetime, if you really want to pass time portions.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, August 17, 2020 9:38 PM
  • Hi ITBobbyP,

    In order to close this thread, please remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

    Thank you for understanding!

    Best regards
    Melissa


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Tuesday, August 18, 2020 6:43 AM