May 11, 2015 at 2:20 pm
I have a stored procedure that runs every 5 minutes. I have one block in the procedure that will only run if there are records in a temp table. In addition, I would like this block to run only if the current time is between 0 and 5 minutes past the hour or between 30 and 35 minutes past the hour.
Currently, my block looks like this:
IF OBJECT_ID('tempdb..#tmpClosedPOs') IS NOT NULL
BEGIN
blah
blah
blah
I also know that I can get the current minutes of the current time by using:
Select DATEPART(MINUTE,GetDate())
I know that it should be simple, but I'm pretty new at Stored Procedures. How do I alter the IF statement to check for the time and only run the block if it's between the times I stated? I started to DECLARE @Minutes INT, but wasn't sure where to go from there. I'm sure this is an easy problem, I'm just too much of a newbie.
Thanks,
Kevin
May 11, 2015 at 2:50 pm
You should be able to directly add the DATEPART condition to your IF statement, as below.
CREATE PROCEDURE MyProcedure
AS
BEGIN
IF OBJECT_ID('tempdb..#tmpClosedPOs') IS NOT NULL AND DATEPART(MINUTE,GetDate()) <= 5
BEGIN
PRINT 'blah'
END
END
GO
May 11, 2015 at 2:53 pm
skilly2 (5/11/2015)
I have a stored procedure that runs every 5 minutes. I have one block in the procedure that will only run if there are records in a temp table. In addition, I would like this block to run only if the current time is between 0 and 5 minutes past the hour or between 30 and 35 minutes past the hour.Currently, my block looks like this:
IF OBJECT_ID('tempdb..#tmpClosedPOs') IS NOT NULLBEGIN
blah
blah
blah
I also know that I can get the current minutes of the current time by using:
Select DATEPART(MINUTE,GetDate())I know that it should be simple, but I'm pretty new at Stored Procedures. How do I alter the IF statement to check for the time and only run the block if it's between the times I stated? I started to DECLARE @Minutes INT, but wasn't sure where to go from there. I'm sure this is an easy problem, I'm just too much of a newbie.
Thanks,
Kevin
Quick solution that might be what you are looking for
😎
USE tempdb;
GO
SET NOCOUNT ON;
/* Sample data set, time sequence with one second increments */
DECLARE @SEC_IN_HOUR INT = 3600;
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP(@SEC_IN_HOUR) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
,TIME_SEQUENCE AS
(
SELECT
DATEADD(SECOND,NM.N,CONVERT(TIME(0),'08:00:00',0)) AS T_TIME
FROM NUMS NM
)
/* This query returns 1 if the value is between 0-5 min passed the half hour */
SELECT
TS.T_TIME
,CASE
WHEN DATEDIFF(SECOND,CONVERT(TIME(0),'00:00:00',0),TS.T_TIME) % 1800 < 301 THEN 1
ELSE 0
END AS TRIGGER_FLAG
FROM TIME_SEQUENCE TS;
May 11, 2015 at 4:24 pm
Personally, I would stick with the simpler / more straight forward:
IF DATEPART(MINUTE,GetDate()) BETWEEN 00 AND 05
OR DATEPART(MINUTE,GetDate()) BETWEEN 30 AND 35
BEGIN
...
END
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 11, 2015 at 4:36 pm
Thanks Andrew! I didn't realize I could use DatePart directly in the IF statement. For some reason I was thinking I had to somehow get it into a variable with the SELECT Statement. Below is the final version of the IF Statement I'm using (to basically have the code block run once every half hour, while the rest of the stored procedure is scheduled to run every 5 minutes. IF OBJECT_ID('tempdb..#tmpClosedPOs') IS NOT NULL
AND (DATEPART(MINUTE,GETDATE()) < 5 OR
(DATEPART(MINUTE,GETDATE()) >= 30 AND DATEPART(MINUTE,GETDATE()) < 35))
Thanks for getting me there!
May 11, 2015 at 4:40 pm
Yeah - I felt pretty embarrassed when I saw Scott's reply and realized I'd missed part of the question. Good to see you've found a solution.
May 11, 2015 at 4:42 pm
After reading Scott's reply, I further simplified the code to: IF (DATEPART(MINUTE,GETDATE()) BETWEEN 00 AND 04
OR DATEPART(MINUTE,GETDATE()) BETWEEN 30 AND 34)
AND OBJECT_ID('tempdb..#tmpClosedPOs') IS NOT NULL
I had to change it to not include 05 and 35 because it's possible it would run twice during that five minute period. But more importantly, I didn't realize BETWEEN was a valid qualifier, so thanks for the info Scott.
May 11, 2015 at 4:47 pm
Eirikur Eiriksson (5/11/2015)
skilly2 (5/11/2015)
I have a stored procedure that runs every 5 minutes. I have one block in the procedure that will only run if there are records in a temp table. In addition, I would like this block to run only if the current time is between 0 and 5 minutes past the hour or between 30 and 35 minutes past the hour.Currently, my block looks like this:
IF OBJECT_ID('tempdb..#tmpClosedPOs') IS NOT NULLBEGIN
blah
blah
blah
I also know that I can get the current minutes of the current time by using:
Select DATEPART(MINUTE,GetDate())I know that it should be simple, but I'm pretty new at Stored Procedures. How do I alter the IF statement to check for the time and only run the block if it's between the times I stated? I started to DECLARE @Minutes INT, but wasn't sure where to go from there. I'm sure this is an easy problem, I'm just too much of a newbie.
Thanks,
Kevin
Quick solution that might be what you are looking for
😎
USE tempdb;GO
SET NOCOUNT ON;
/* Sample data set, time sequence with one second increments */
DECLARE @SEC_IN_HOUR INT = 3600;
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP(@SEC_IN_HOUR) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
,TIME_SEQUENCE AS
(
SELECT
DATEADD(SECOND,NM.N,CONVERT(TIME(0),'08:00:00',0)) AS T_TIME
FROM NUMS NM
)
/* This query returns 1 if the value is between 0-5 min passed the half hour */
SELECT
TS.T_TIME
,CASE
WHEN DATEDIFF(SECOND,CONVERT(TIME(0),'00:00:00',0),TS.T_TIME) % 1800 < 301 THEN 1
ELSE 0
END AS TRIGGER_FLAG
FROM TIME_SEQUENCE TS;
Thanks Eirikur. I went with the simpler solution although yours might work just as well. I didn't get around to testing it, but appreciate the alternate technique so I can learn more with SQL!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply