Deciding if within a certain timeframe

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Deciding if within a certain timeframe

Author

Topic

jvarchitect
Starting Member

2 Posts

Posted - 2013-08-14 : 14:03:03

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-14 : 14:39:54

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)
)

jvarchitect
Starting Member

2 Posts

Posted - 2013-08-15 : 08:28:04

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-15 : 09:27:11

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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-08-19 : 06:19:40

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources