Change value returned by getdate()

Question

Change value returned by getdate()

hussain27syed

Ten Centuries

Points: 1191
More actions
July 11, 2009 at 7:55 am

#136163

Hi There,
i need a lil help regarding getdate().
Is there a way to get a different datetime when i select getdate().
something like set getdate()= user define time.
i am developing a few SSIS packages that would be running on the server. Many of them are have fields like upload date, file creation date for which i am using getdate() .
however,sometimes the servers might go down and so i would need to run the jobs on the next day. however i want the date fields to be populated as yestrday's date.
The volumes of tables rule out the possibilty nof manually updating them.
I will be running the delayed jobs manually. hence i thought of using an execute sql task to load the getdate time into a variable and using that varaible everywhere else in the package. With this i can always modify the task to say getdate-1 to get previous day's date.
However, i am not able to pass the varaible as parameters to many of the tasks due to various reasons.
Hence i want a way to know if there is a way to change the value of getdate() . The change value should be held till the package executes.

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

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

russell-154600 Ten Centuries Points: 1375 More actions · Answer 1

you really need to use a variable. perhaps you can implement some logic to determine if it is today's or yesterday's report.

why do you say you can't use a variable?

Lowell SSC Guru Points: 323518 More actions · Answer 2

why not create a user defined function on the server, ie dbo.mydate()

that returns a specific datetime value; it could return something like the calculated last business day, or a date from a table or something,

then you could update the table prior to your package, so you could change it easily without having to ALTER the function.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

hussain27syed Ten Centuries Points: 1191 More actions · Answer 3

Thanks for the replies

@russel

I have tried using variables but it dint work out for me .

In data flow task i use ? in place of the variable, but when i click on the "Parameters" button, i get an error saying that certain SQL paramets cant be parsed and that i need to use a script task to set that select query in a variable.

Also i have multiple delete/update scripts running in the "same execute sql task" and i am not able to pass the parameters to them

@lowell

Your suggestion should work for me. only hitch would be there might be a other packages running on the server at that time. I dont want to others to be affected by this.