July 22, 2009 at 12:40 am
Hi,
could you please help me in creating a function that would return the same value as getdate().:hehe:
I have never created a sql function, so forgive me if i am using wrong syntax.
this is what i tried first
CREATE FUNCTION [getTodaydate]
as
BEGIN
RETURN (getdate())
END
It dint work. i get an error that says i cannot use a return value with return statement:rolleyes:
So i tried to create a view that would return todays date .
create view getTodaydate
as select getdate() as [Today's Date]
and then retrive value from this view. something like
create function getTodaydate
as
begin
(
Declare @date datetime
set @date= (select [Today's Date] from getTodaydate)
return(@date)
end
)
But this doesnt work either.
What i am trying to do might sound funny but thats my requirement:-D
I have lots of SSIS packages running in server. I have used getdate() to fill columns like [date of Upload] etc.
Sometimes i have to use yesterday's date as [date of Upload] . With a user defined functon it becomes easy to alter it to get yesterdays date. this way i can run the job without having to modify the package.
July 22, 2009 at 1:34 am
There is perhaps a more elegant way of achieving what you want - check out "Creating Package Configurations" in BOL.
You could have a variable called (say) LoadDate which is assigned from your config file. Leave it blank unless you want to use a date other than 'run date'.
Then use a piece of script to assign 'today' to the variable, unless it already has a date assigned & then use that variable in your data flow.
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.
July 22, 2009 at 3:10 am
Hi Phil
thanks for the reply mate.
I have tried to do this with a variable. I have added that variable to Config file.
I used a query Select getdate() and assigned the returned value to another variable which i use throughout the package.
But surprisingly i am not able to pass this variable as parameter to data flow tasks and execute sql tasks.
When i give ? in a select query and click on parameters. it shows an error saying it could not parse the query and suggests to use the whole SQL Query in a variable.
I have packages some of which have 30-40 data flow/execute sql tasks. Also i use getdate() many times n a single task. Considering the volume, it would consume lot of efforts to get the run date from a variable.
Hence i wanted to use a function which would reduce the efforts required.
July 22, 2009 at 3:42 am
In an Execute SQL task, if you want to build a dynamic piece of SQL that includes variables, use the 'Expressions' area to override the SQLStatementSource property & this should work.
But I'm understanding why you are going down this path now.
Try this for your function:
Create Function dbo.RunDate() Returns Datetime
as
Begin
Declare @Return Datetime
Select @Return = Getdate()
Return @Return
End
Phil
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.
July 22, 2009 at 4:12 am
Thanks for the code Phil.
this is what i was exactly looking for
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply