August 5, 2013 at 10:11 am
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
August 5, 2013 at 10:20 am
you don't need the SELECT next to the GETDATE() , remove that and it should work.
August 5, 2013 at 10:24 am
Thanks its saved that time, Ill wait till tomorrow and see if it runs. 🙂
August 5, 2013 at 10:50 am
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/
August 5, 2013 at 10:56 am
Ah I see, ok Ill wait till after 9am tomorrow and check for results. Thanks Again! 🙂
August 5, 2013 at 11:59 am
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
August 5, 2013 at 12:36 pm
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/
August 5, 2013 at 4:55 pm
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
Change is inevitable... Change for the better is not.
August 6, 2013 at 7:39 am
Hi Jeff,
That's what I say, but my boss doesn't want to do that. I am on the lowest rung here. 🙂
August 6, 2013 at 8:00 am
Does he want to track suspecting other users/team running the SP or not?
Regards
Durai Nagarajan
August 6, 2013 at 8:45 am
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.
August 6, 2013 at 8:48 am
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/
August 6, 2013 at 8:59 am
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
August 7, 2013 at 9:31 am
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.
August 7, 2013 at 9:44 am
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.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply