locked
Comparing Datetime RRS feed

  • Question

  • Happy holidays!

    Ok, I have a question that seems basic, but I am having major issues. I need to compare the following date/times. Basically I need to select from a table where the date range is between '12/15/2013' and '12/21/2013' and time is BETWEEN 5:00PM and 8:00AM. I cannot seem to get this working. The date is simply a week range.

    Here is what I have... INTERVALSTART is in the following format (datetime): '2013-12-15 18:30:00.000'.  Any help is greatly appreciated.

    SELECT sum(abncalls), INTERVALSTART
    FROM
    oadb.hCMSSkill
    WHERE INTERVALSTART >= '12/15/2013 17:00:00.000' AND
    INTERVALSTART <= '12/21/2013 08:00:00.000'

    Dave SQL Developer

    Monday, December 30, 2013 6:57 PM

Answers

  • I see please use the below one as reference

    Declare @DateTable Table (WorkTime Datetime)
    Insert Into @DateTable
    Values ('12/15/2013 17:00:00.000'),
           ('12/15/2013 14:00:00.000'),
           ('12/15/2013 11:00:00.000'),
    	   ('12/16/2013 19:00:00.000'),
    	   ('12/21/2013 08:30:00.000'),
    	   ('12/18/2013 08:00:00.000'),
    	   ('12/19/2013 07:00:00.000')
    
     
    	   Select *
    	   From @DateTable
    	   Where WorkTime>=convert(datetime,'12/15/2013 17:00:00.000') And 
    	   WorkTime<=convert(datetime,'12/21/2013 08:00:00.000')
    	   And (Case When DATEPART(HOUR,WorkTime)>=12 Then DATEPART(HOUR,WorkTime) Else 24 End )>=17
    	   And (Case When DATEPART(HOUR,WorkTime)<12 Then  DATEPART(HOUR,WorkTime) Else 0 End )<=8

    So in your case it will be
    SELECT sum(abncalls), INTERVALSTART
    FROM
    oadb.hCMSSkill
    WHERE INTERVALSTART
    --Try Below
     >= convert(datetime,'12/15/2013 17:00:00.000') 
     And INTERVALSTART<=convert(datetime,'12/21/2013 08:00:00.000')
     And (Case When DATEPART(HOUR,INTERVALSTART)>=12 Then DATEPART(HOUR,INTERVALSTART) Else 24 End )>=17
     And (Case When DATEPART(HOUR,INTERVALSTART)<12 Then  DATEPART(HOUR,INTERVALSTART) Else 0 End )<=8

    • Marked as answer by DaveDB Monday, December 30, 2013 7:58 PM
    Monday, December 30, 2013 7:41 PM

All replies

  • SELECT sum(abncalls), INTERVALSTART
    FROM
    oadb.hCMSSkill
    WHERE INTERVALSTART
    --Try Below
    between convert(datetime,'12/15/2013 17:00:00.000') And convert(datetime,'12/21/2013 08:00:00.000')

    Happy Holidays :)
    • Edited by Taherul673 Monday, December 30, 2013 7:05 PM
    Monday, December 30, 2013 7:03 PM
  • Thanks for your response :)

    I still see time from 8:30AM on. Basically I need to display that week and only NON-work hours during that week. So from 5:00PM - 8:00AM every day within that week.


    Dave SQL Developer

    Monday, December 30, 2013 7:13 PM
  • I see please use the below one as reference

    Declare @DateTable Table (WorkTime Datetime)
    Insert Into @DateTable
    Values ('12/15/2013 17:00:00.000'),
           ('12/15/2013 14:00:00.000'),
           ('12/15/2013 11:00:00.000'),
    	   ('12/16/2013 19:00:00.000'),
    	   ('12/21/2013 08:30:00.000'),
    	   ('12/18/2013 08:00:00.000'),
    	   ('12/19/2013 07:00:00.000')
    
     
    	   Select *
    	   From @DateTable
    	   Where WorkTime>=convert(datetime,'12/15/2013 17:00:00.000') And 
    	   WorkTime<=convert(datetime,'12/21/2013 08:00:00.000')
    	   And (Case When DATEPART(HOUR,WorkTime)>=12 Then DATEPART(HOUR,WorkTime) Else 24 End )>=17
    	   And (Case When DATEPART(HOUR,WorkTime)<12 Then  DATEPART(HOUR,WorkTime) Else 0 End )<=8

    So in your case it will be
    SELECT sum(abncalls), INTERVALSTART
    FROM
    oadb.hCMSSkill
    WHERE INTERVALSTART
    --Try Below
     >= convert(datetime,'12/15/2013 17:00:00.000') 
     And INTERVALSTART<=convert(datetime,'12/21/2013 08:00:00.000')
     And (Case When DATEPART(HOUR,INTERVALSTART)>=12 Then DATEPART(HOUR,INTERVALSTART) Else 24 End )>=17
     And (Case When DATEPART(HOUR,INTERVALSTART)<12 Then  DATEPART(HOUR,INTERVALSTART) Else 0 End )<=8

    • Marked as answer by DaveDB Monday, December 30, 2013 7:58 PM
    Monday, December 30, 2013 7:41 PM
  • Select * from   oadb.hCMSSkill
     WHERE INTERVALSTART >= '12/15/2013' AND  INTERVALSTART < Dateadd(day,1,'12/21/2013') 
    AND (datepart(hour,INTERVALSTART) < 8 OR datepart(hour,INTERVALSTART)>17)

    • Proposed as answer by Taherul673 Monday, December 30, 2013 7:53 PM
    Monday, December 30, 2013 7:50 PM
  • Select * from   oadb.hCMSSkill
     WHERE INTERVALSTART >= '12/15/2013' AND  INTERVALSTART < Dateadd(day,1,'12/21/2013') 
    AND (datepart(hour,INTERVALSTART) < 8 OR datepart(hour,INTERVALSTART)>17)


    Simple and the one you need I guess.
    Monday, December 30, 2013 7:54 PM
  • What is your SQL Server version?

    SELECT sum(abncalls), INTERVALSTART FROM oadb.hCMSSkill WHERE INTERVALSTART BETWEEN '2013-12-15T17:00:00.000' AND '2013-12-21T08:00:00.000' AND

    (CAST(IntervalStart AS TIME) between '17:00:00' AND '24:00:00'

    OR CAST(IntervalStart as TIME) BETWEEN '00:00:00' AND '08:00:00')



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


    My blog


    My TechNet articles


    • Edited by Naomi N Monday, December 30, 2013 7:59 PM
    • Proposed as answer by Taherul673 Monday, December 30, 2013 8:03 PM
    Monday, December 30, 2013 7:57 PM
  • Perfect, thanks!

    Dave SQL Developer

    Monday, December 30, 2013 7:58 PM
  • You are welcome but I would go for Jingyang or Naomi's solution because of simplicity. Anyways...Thanks! :)
    Monday, December 30, 2013 8:04 PM
  • >> I need to compare the following date/times. Basically I need to select from a table where the date range is between '12/15/2013' and '12/21/2013' and time is BETWEEN 5:00PM and 8:00AM. I cannot seem to get this working. The date is simply a week range. <<

    No, it is a pile of dialect that violates ANSI/ISO Standard SQL and fights the direction that Microsoft has taken. In SQL, we would write a DATETIME2(0) today.  You do not need the old DATETIME with its weird decimal seconds anymore!  Hooray!

    But a SUM() has to be grouped by something; a date? An hour? By the seconds? 

    Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise. 

    CREATE TABLE Something_Intervals
    (something_interval_name CHAR(15) NOT NULL PRIMARY KEY
       CHECK (something_report_name LIKE <pattern>),
     something_report_start_datetime DATETME2(0) NOT NULL,
     something_report_end_date DATETIME2(0) NOT NULL,
      CONSTRAINT date_ordering
        CHECK (something_report_start_datetime <= something_report_end_datetime),
    etc);

    Now use a BETWEEN predicate and group by  something_interval_name

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, December 30, 2013 11:07 PM