Create a function to get todays date

Question

Create a function to get todays date

hussain27syed

Ten Centuries

Points: 1191
More actions
July 22, 2009 at 12:40 am

#155997

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.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

hussain27syed Ten Centuries Points: 1191 More actions · Answer 2

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.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

hussain27syed Ten Centuries Points: 1191 More actions · Answer 4

Thanks for the code Phil.

this is what i was exactly looking for