SQL Server Performance

Update all Date/Time Fields in Database

Discussion in 'ALL SQL SERVER QUESTIONS' started by Gary Dixon, Sep 18, 2012.

  1. Gary Dixon New Member

    Hi this is my first post here at SQLServer Performance.

    Wondered if anyone could help:

    I have a database from 2009 with a bunch of test data.

    I want to replace all the date/time fields in the source data with current dates - so that the year is updated to 2012.

    The context is that I'm loading this data via SSIS into weekly partitions into a data mart. I created the data mart partitions from todays date. and do not want to create partitions going back to 2009.

    What's the syntax for replacing all the 2009's in every Date/Time field in the entire database with 2012's?
    Gary Dixon, Sep 18, 2012
    #1

  2. Shehap Moderator

    Welcome to Forums,

    In order to replace all the 2009's in every Date/Time field in the entire database with 2012's , you may use the below query

    declare@datedatetime=getdate()

    select@date

    set@date=Dateadd(y,3,@date)

    Kindly work out it and let me know your feedback
    Shehap, Sep 18, 2012
    #2

  3. FrankKalis Moderator

    To expand to Shehap's answer: You could query sys.tables JOIN sys.columns for all datetime types you're interested in and get the table name + column name(s) back from that. Put this all in a dynamically generated UPDATE statement and run this for each table. The statement(s) are not that difficult, but I guess quite time-consuming to get it right.
    FrankKalis, Sep 18, 2012
    #3

Share This Page