Run at Certain Time of Morning

Question

Post reply

Run at Certain Time of Morning

OldSageMonkey

SSC Veteran

Points: 265
More actions
August 5, 2013 at 10:11 am

#280869

Hi All,
Im trying to add a check to see what time it is in the morning before my stored procedure runs, I tried looking around for some examples but Im sure the simple solution is right before my eyes.
AS
declare @today as date = cast(getdate()-1 as date)
declare @HTML as varchar(max)
declare @TitleHTML as varchar(max)
declare @CDCIIHTML as varchar (max)
declare @CDCsubj as varchar(20) = 'CDC Report'
set @TitleHTML =
N'<h1 align="Left">CDC Report</h1>'+
N'<h2 align="Left"> ' + CAST(GetDate()as varchar) + '</h2>'
set @CDCIIHTML =
N'<h2 align="Left"> CDC CII </h2>'+
N'<style> td {border: solid black 1px; text align: center; padding-left:5px;padding-right:6px;padding-top:1px;padding-bottom:1px;font-size:10pt;}</style>'+
N'<table> <width="100%" cellpadding="5"> <table style="font-family: Calibri">'+
N'<tr bgcolor="gray">
<td width ="150" ><b>SCHEDULE</b></td>
<td width ="100"><b>NC</b></td>'+
N'<td width ="100"><b>DESCRIPTION</b></td>'+
N'<td width ="175"><b>UNIT</b></td>'+
N'<td width ="125"><b>VAR PCT</b></td>'+
N'<td width ="100"><b>QTY</b></td>
<td width ="125"><b>USER ID</b></td>'+
N'<td width ="200"><b>DATE</b></td>'+
N'<td width ="250"><b>REASON</b></td>'+
CAST((
SELECT
td = [SCHEDULE],'',
td = [NDC],'',
td = [DESCRIPTION],'',
td = [TOUNIT],'',
td = [CYCLEVARPCT],'',
td = [CYCLE QTY],'',
td = isnull([USERID],''),'',
td = [TO_CHAR(A.TIMEDATE,'MM/DD/YYYY')],'',
td = isnull([REASON],''),''
FROM
[CF].[dbo].[CDC Report]
WHERE SCHEDULE = 'CII'
FOR XML PATH('tr'), TYPE
)AS NVARCHAR(MAX)) + N'</table>'
SET @HTML = @TitleHtml + isnull(@CDCIIHTML,'')
if (@HTML IS not null) AND (SELECT GETDATE() >= '09:00:00') --telling me here I have incorrect --syntax, and I cant figure out why? At the greater than and closing parenthesis after 9am.
begin
EXEC
msdb.dbo.sp_Send_dbmail
@profile_name = 'SQL_Server',
@recipients = ''
@subject = @CDCsubj,
@body_format='HTML',
@body = @HTML
END
Thanks

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

you don't need the SELECT next to the GETDATE() , remove that and it should work.

OldSageMonkey SSC Veteran Points: 265 More actions · Answer 2

Thanks its saved that time, Ill wait till tomorrow and see if it runs. 🙂

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

OldSageMonkey (8/5/2013)
Thanks its saved that time, Ill wait till tomorrow and see if it runs. 🙂

It will run and it will be successful no matter what time it actually runs. Your check for GETDATE() >= '09:00:00' will not correctly check for a given time of day. You need to compare against a datetime. What is happening here is you will get an implicit conversion to datetime and as long as your system date is after January 1st, 1900 at 9am the IF condition will evaluate to true.

Try this.

select CAST('09:00:00' as datetime)

Instead you need to do some date math in your check.

if getdate() >= dateadd(HOUR, 9, dateadd(DAY, datediff(DAY, 0, getdate()), 0))

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

OldSageMonkey SSC Veteran Points: 265 More actions · Answer 4

Ah I see, ok Ill wait till after 9am tomorrow and check for results. Thanks Again! 🙂

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 5

Sean Lange (8/5/2013)
OldSageMonkey (8/5/2013)
Thanks its saved that time, Ill wait till tomorrow and see if it runs. 🙂
It will run and it will be successful no matter what time it actually runs. Your check for GETDATE() >= '09:00:00' will not correctly check for a given time of day. You need to compare against a datetime. What is happening here is you will get an implicit conversion to datetime and as long as your system date is after January 1st, 1900 at 9am the IF condition will evaluate to true.
Try this.
select CAST('09:00:00' as datetime)
Instead you need to do some date math in your check.
if getdate() >= dateadd(HOUR, 9, dateadd(DAY, datediff(DAY, 0, getdate()), 0))

My be a bit simpler to read this:

IF cast(getdate() as time) >= '09:00'

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

Jeffrey Williams 3188 (8/5/2013)
My be a bit simpler to read this:
IF cast(getdate() as time) >= '09:00'

Aye!!! That it is.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

OldSageMonkey (8/5/2013)
Hi All,
Im trying to add a check to see what time it is in the morning before my stored procedure runs

Why? I might be missing something here but why not just set the stored procedure to run as a job at the time you want it to run?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

OldSageMonkey SSC Veteran Points: 265 More actions · Answer 8

Hi Jeff,

That's what I say, but my boss doesn't want to do that. I am on the lowest rung here. 🙂

durai nagarajan SSCoach Points: 15775 More actions · Answer 9

Does he want to track suspecting other users/team running the SP or not?

Regards
Durai Nagarajan

OldSageMonkey SSC Veteran Points: 265 More actions · Answer 10

Hi Durai,

No we are the only ones that use the server, its ours. I tried to make my suggestion to schedule a job and that was that.

Sean Lange SSC Guru Points: 286573 More actions · Answer 11

OldSageMonkey (8/6/2013)
Hi Durai,
No we are the only ones that use the server, its ours. I tried to make my suggestion to schedule a job and that was that.

So if you isn't a scheduled job how do you run it?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

durai nagarajan SSCoach Points: 15775 More actions · Answer 12

OldSageMonkey (8/6/2013)
Hi Durai,
No we are the only ones that use the server, its ours. I tried to make my suggestion to schedule a job and that was that.

if it is not tracking , what are you doing by sending mail then?

as sean asked i too want to know how are you executing the SP.

Regards
Durai Nagarajan

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 13

Also, if you just want to see that it is past 9 AM, you could alternatively use the following to avoid CASTing or CONVERTing:

DATEPART(hh,GETDATE()) >= 9

Cheers!

P.S. I apologize if anyone else is seeing my '>' getting replaced. Preview always shows my post using the '>' symbol, but the actual post ends up using that markup substitute.

OldSageMonkey SSC Veteran Points: 265 More actions · Answer 14

Hi Durai,

After asking, it turns out that my boss has an import job scheduled at a certain time. But I do not have permissions to access the jobs etc.