September 27, 2019 at 9:48 am
Hi,
I have a requirement to only execute the SP on tuesday and friday. The SP is called within the SSIS package and I have tried to make the change in the SP itself. I am using Netezza so included the following in the SP but I am getting errors when executing the SP.
IF DATE_PART('DOW',CURRENT_DATE) IN ('3','6') -- this is to check if the days of the week are tuesday and friday.
BEGIN
Can someone please give some insights on how to make this work ? Thanks.
September 27, 2019 at 10:13 am
Looks like Netazza syntax is different from SQL Server syntax. Not sure there's anyone here who will be able to help you - you may be better off on a Netazza forum. The T-SQL statement would look something like this:
IF DATEPART('dw',CURRENT_TIMESTAMP) IN (3,6)...What error are you getting, incidentally?
John
September 27, 2019 at 10:21 am
The Netezza syntax is okay and equivalent of SQL server but I think I am not using it correctly in the SP.
The error I am getting is - ERROR: syntax error, unexpected IF, expecting BEGIN at or near "IF"
Do you know how to use this properly within the SP for this to work ? Thanks.
September 27, 2019 at 10:25 am
That doesn't sound like a SQL Server error message. Do you get the same error message if you call the stored procedure from SSMS instead of from the SSIS package? Please post the whole stored procedure definition.
John
September 27, 2019 at 10:50 am
try this - its just a guess
IF (DATE_PART('DOW',CURRENT_DATE) IN ('3','6'))
begin
(put brackets around your evaluation)
MVDBA
September 27, 2019 at 12:25 pm
CREATE PROCEDURE #TestDW
(
@CheckDayOfWeek bit = 0
) AS
IF @CheckDayOfWeek = 0 OR DATEPART(dw,GETDATE()) IN (3,6) BEGIN
PRINT 'Hello'
/* Rest of SP here */
END
GO
/* Run with date check that today is Tuesday or Friday (3, 6) */
EXEC #TestDW 1
GO
/* Run sp whatever, don't bother to check date */
EXEC #TestDW 0
September 27, 2019 at 2:10 pm
The Netezza syntax is okay and equivalent of SQL server
Based on what you posted, it really isn't.
Or if it is, where did you get your syntax from?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 27, 2019 at 6:25 pm
Based on my reading of Netezza syntax online:
IF DATE_PART('DOW',CURRENT_DATE) IN ('3','6') THEN
statement
[statement] ...
END IF;
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply