Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Comparing datetime Expand / Collapse
Author
Message
Posted Monday, December 30, 2013 11:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 24, 2014 11:06 AM
Points: 109, Visits: 215
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.

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'
Post #1526572
Posted Monday, December 30, 2013 12:03 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:51 AM
Points: 1,510, Visits: 8,470
It would be helpful if you told us what issues you're experiencing.



Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #1526576
Posted Monday, December 30, 2013 12:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 24, 2014 11:06 AM
Points: 109, Visits: 215
Thanks for your response.

My issue is that I am not able to get the correct values. The date range values are fine, but the times are off. I shouldn't see any time between work hours (8:00AM - 5:00PM), but I am still seeing those times.

I hope this explains my issue.
Post #1526577
Posted Monday, December 30, 2013 12:18 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:51 AM
Points: 1,510, Visits: 8,470
Your problem is that you have not restricted the times as you wanted to.

You're asking for values between 12/15/2013 17:00:00 and 12/21/2013 08:00:00 not between 12/15/2013 and 12/21/2013 only where the time is between 5:00PM and 8:00AM.

You need to add another condition to the where clause that only looks at the time portion of the date and restrict that to only times between 5:00PM and 8:00AM.




Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #1526581
Posted Monday, December 30, 2013 12:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 24, 2014 11:06 AM
Points: 109, Visits: 215
Thanks Alvin. I will try that. Happy New Year to you and thanks for your time
Post #1526582
Posted Monday, December 30, 2013 12:21 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:51 AM
Points: 1,510, Visits: 8,470
You're welcome and Happy New Year to you too.

Let us know if you have any more issues.




Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #1526584
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse