SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Deciding if within a certain timeframe
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jvarchitect
Starting Member

USA
2 Posts

Posted - 08/14/2013 :  14:03:03  Show Profile  Reply with Quote
Hey All,

New to the board so let me know if I'm posting in the right place. Having an issue with this statement.

Essentially, I have a table that highlights a process and the start and end time of that process. I already run a convert to varchar to manipulate the time and am going to give you in the format that the time is in after the convert. (sorry if formatting is a little off)

Process
Start
End

Work
8:00AM
5:00PM

Dishes
8:00PM
8:20PM

Sleep
11:00PM
6:00AM

Walk the Dog
6:30AM
7:00AM


Now, I'm trying to query to see if I am within a 10 minute window of any of the start or end times (using getdate for the server time). If I can't do that, I'd love to be able to determine any time frame that is not within a 10 minute window of ANY of the start and stop times. If this is not realistic, let me know.

Thanks in advance!

- M



- M

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 08/14/2013 :  14:39:54  Show Profile  Reply with Quote
From what I understood of your description, this:
SELECT * FROM YourTable
WHERE 
(
	CAST([START] AS TIME) >= CAST(DATEADD(mi, -10, GETDATE()) AS TIME)
	AND CAST([START] AS TIME) <= CAST(DATEADD(mi, 10, GETDATE()) AS TIME)
)
OR
(
	CAST([END] AS TIME) >= CAST(DATEADD(mi, -10, GETDATE()) AS TIME)
	AND CAST([END] AS TIME) <= CAST(DATEADD(mi, 10, GETDATE()) AS TIME)
)
Go to Top of Page

jvarchitect
Starting Member

USA
2 Posts

Posted - 08/15/2013 :  08:28:04  Show Profile  Reply with Quote
This works perfectly! Thanks James. Anyway on this forum to close out an answered question and/or give you credit for a great answer?

- M
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 08/15/2013 :  09:27:11  Show Profile  Reply with Quote
You are very welcome - glad to help.

SQLTeam does not have the concept of marking threads as answered/closed. If you leave it alone, after a while activity trails off and it dies a natural death.

Nobody gets any credits either. My guess is that most people who answer questions do it for the joy they get out of solving problems, and for the opportunity it affords them to learn and sharpen their own skills. I know it is so for me.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 08/19/2013 :  06:19:40  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by jvarchitect

This works perfectly! Thanks James. Anyway on this forum to close out an answered question and/or give you credit for a great answer?

- M


You can edit the subject of this thread and add [Solved] at the end

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.39 seconds. Powered By: Snitz Forums 2000