Need help with querying dates and times

  • Brad Allison

    Hall of Fame

    Points: 3473

    Okay, so I have a new report that needs to get data for telephone calls on certain DNIS lines. They want start date and end date - no problem. But they also only want calls that are from 8AM to 5PM. We have several records for phone lines during off hours also. I can do a start date and end date, but how would I filter for that time frame for every date in between? The only thing I can think of is do some DATEPART concatenation and create a temp table with using just the date and then a field for the time and deleting records from that table where there are calls outside of that time range, then remerging those records into the returned dataset?

    Is there any other easier way to do this? Thanks

  • Ken McKelvey

    SSCoach

    Points: 18234

    The normal approach is to join to a calendar table for the datetime between the given times.

  • Luis Cazares

    SSC Guru

    Points: 183568

    Here's an option that can help.

    IF OBJECT_ID('dbo.DateTimeTable') IS NOT NULL

    DROP TABLE dbo.DateTimeTable;

    CREATE TABLE dbo.DateTimeTable( MyDatetime datetime);

    CREATE CLUSTERED INDEX CI_MyDatetime ON dbo.DateTimeTable(MyDatetime);

    INSERT INTO dbo.DateTimeTable

    SELECT TOP 10000 CAST( '2010' AS datetime) + RAND(CHECKSUM(NEWID()))*10000

    FROM sys.all_columns a, sys.all_columns b;

    DECLARE @StartDate datetime = '20161201',

    @EndDate datetime = '20161210';

    SET STATISTICS XML ON;

    SELECT *

    FROM dbo.DateTimeTable

    WHERE MyDatetime >= @StartDate

    AND MyDatetime < @EndDate + 1

    AND CAST(MyDatetime AS time) >= '08:00'

    AND CAST(MyDatetime AS time) < '17:00';

    SET STATISTICS XML OFF;

    DROP TABLE dbo.DateTimeTable;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares

    SSC Guru

    Points: 183568

    Ken McKelvey (12/7/2016)


    The normal approach is to join to a calendar table for the datetime between the given times.

    I'm not sure about this approach. If the datetime column can have any time, then the calendar table would be too large to handle joins.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • ZZartin

    SSC-Dedicated

    Points: 30355

    The logic shouldn't be too complicated add your normal filter in the where clause for the start and end dates which it sounds like you already have then add another filter that looks something like

    AND DATEDIFF(hour, DATEADD(day, 0, DATEDIFF(day, 0, CALL_DATE_TIME)), CALL_DATE_TIME) >= 8 AND DATEDIFF(hour, DATEADD(day, 0, DATEDIFF(day, 0, CALL_DATE_TIME)), CALL_DATE_TIME) < 17

    Depending on the volume of calls there might be some performance tweeking that would need to be done.

  • TheSQLGuru

    SSC Guru

    Points: 134017

    A minor point, but if you don't need much precision on the seconds this will save you 4 bytes per row that is processed during the actual CONVERTs:

    DECLARE @StartDate datetime = '20161201',

    @EndDate datetime = '20161210';

    SELECT *

    FROM dbo.DateTimeTable

    WHERE MyDatetime >= @StartDate

    AND MyDatetime < @EndDate + 1

    AND CAST(MyDatetime AS time(2)) >= CAST('08:00' AS time(2))

    AND CAST(MyDatetime AS time(2)) < CAST('17:00' AS time(2));

    Defaulted time datatype is time(7), which is 5 bytes in size. time(0) - time(3) takes only 3 bytes. time(0) is what most people really should use I think because they only have whole seconds, not fractional ones.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Luis Cazares

    SSC Guru

    Points: 183568

    ZZartin (12/7/2016)


    The logic shouldn't be too complicated add your normal filter in the where clause for the start and end dates which it sounds like you already have then add another filter that looks something like

    AND DATEDIFF(hour, DATEADD(day, 0, DATEDIFF(day, 0, CALL_DATE_TIME)), CALL_DATE_TIME) >= 8 AND DATEDIFF(hour, DATEADD(day, 0, DATEDIFF(day, 0, CALL_DATE_TIME)), CALL_DATE_TIME) < 17

    Depending on the volume of calls there might be some performance tweeking that would need to be done.

    So much code when you're only need this:

    DATEPART(HH, MyDatetime)

    Either way, that would return incorrect results. I'll let you figure out why.;-)

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • ScottPletcher

    SSC Guru

    Points: 98120

    ...

    WHERE datetime_column >= @StartDate_set_to_0800_AM AND

    datetime_column < DATEADD(DAY, 1, @EndDate_set_to_midnight) AND

    DATEPART(HOUR, datetime_column) BETWEEN 8 AND 16

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply