former int-column (year) to datetime

Question

Post reply

former int-column (year) to datetime

mariogamper68

Newbie

Points: 6
More actions
February 20, 2008 at 9:53 am

#378674

hello folks!
I now have a table which has a column 'xyz' of type int. Now I would like to become a column of type datetime which year-value is the 'old' column 'xyz'. Month- and day-value could be 1.
an example: old column (int) = 2002 --> new column (datetime) 01.01.2002
Does anyone know how I can achive this?
Thanks in advance!
daprodigy

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I would do this is four steps.

1. Create a new column on the table as a datetime column: xyz_new

2. Update the new column with the following code: update MyTable set xyz_new = cast(cast(xyz as char(4)) + '0101' as datetime)

3. Drop the old column: xyz

4. Rename the new column: xyz_new -> xyz

Hope this helps

😎

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 2

Lynn is spot on.

Just to expose another conversion method that's all math and nothing character based...

[font="Courier New"]UPDATE MyTable SET xyz_new = SELECT DATEADD(yy,XYX-1900,0)[/font]

Sounds obvious, but make sure that step 2 actually works all the way through before you execute step 3. I've seen where there is an out of date-range error in things like step 2 and for some reason, folks proceed to step 3...

... and always make sure you have a vialble backup!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Jeff, I knew there was a way to do it with dateadd, I just didn't think it through enough. Good catch!

😎

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

You may want something like this to make sure the number is a valid SQL Server year.

select case when XYX between 1753 and 9999 then dateadd(yy,XYX-1900,0) else null end

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 5

I'm with Michaeal... I'm not sure anyone would have entries for, say, 1754, or even 1901 and certainly not for the year 2100 or 2200. What I'm trying to get at is it would be very wise to check all of the data in this column for something reasonable before trying to do the conversion.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

And I'll third that. That should actually be the first thing you do.

😎

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

Of course, there is probably a check or FK constraint on the column already to make sure it is a valid year, but it doesn't hurt to verify that. 🙂

mariogamper68 Newbie Points: 6 More actions · Answer 8

WOW, thank you all very much for your time!! This is unbelievable 🙂

I took the solution of Lynn and it worked very well!

Many many thanks goes out from switzerland to you!!! You are awesome 😉

greetz

daprodigy

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 9

Nicely done, Lynn!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)