Alter Table Condition

  • texpic

    SSCertifiable

    Points: 5882

    I have a dynamically built table that has columns 20131206 back 30 days to 20131107. Tomorrow 20131207 to 20131108. These are varchar now not datetime.

    Is there a way to ALTER TABLE SET Fieldname RIGHT(2) WHEN LEFT(4) = '2013'

    Thanks

  • PRAMANA.DBA

    SSCertifiable

    Points: 5507

    Can u elaborate u r question?

  • Mark Eckeard

    Hall of Fame

    Points: 3453

    It's hard to say with out more information but you may need to us the update statement, not alter

    UPDATE TABLE SET Fieldname = RIGHT(Fieldname , 2) WHERE LEFT(Fieldname , 4) = '2013'

    Mark

  • Ed Wagner

    SSC Guru

    Points: 286982

    So you have columns named 20131206, 20131107 and 20131108? You're keeping some type of history for 30 days, but why would you store dates as columns instead of storing the dates in rows? That would allow you to maintain history the for the past 29 days by not touching the rows, delete the 30th day ago and add today as the new day 1. You wouldn't have to dynamically rebuild your table (in fact the structure wouldn't change at all) and you could index the table for performance?

    Then again, I might just be misinterpreting the whole thing.

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

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