Post reply

Change value returned by getdate()

  • 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.

  • July 11, 2009 at 8:23 am

    #1023492

    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?

  • July 11, 2009 at 11:36 am

    #1023539

    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!

  • July 12, 2009 at 11:32 pm

    #1023675

    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.

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

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