Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kurmanc
Yak Posting Veteran
92 Posts |
 Posted - 2008-08-20 : 09:05:55
|
Hi,There is a table "Test" that has a column "CompeltedDate" with datatype char(10). I do not like this because it cuts the last number, like this 'Oct 28 200'. So what I want is to change to a mopre correct datatype, DATETIME. Note, There is a very simple short cut to this by increasing from char(10) to char(11), but I want to change the datatype completely.This is a way I tried to fix the problem, but it is not optimal because there is data in the table from 2006, 2007, and 2008. Not only 2008. Any help would be appreciated.....CREATE TABLE #NEWtest (CompletedDate datetime)INSERT INTO #NEWtest (CompletedDate)SELECT CompletedDate + '8' FROM Test EXEC sp_rename '#NEWtest', 'Test' So, as you can see, this will work..the problem is it will affect the other years incorrectly. Even CompletedDates 2006 will be changed to 2008. |
|
|
VGuyz
Posting Yak Master
121 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-20 : 09:27:59
|
quote:
Originally posted by Kurmanc
Hi,There is a table "Test" that has a column "CompeltedDate" with datatype char(10). I do not like this because it cuts the last number, like this 'Oct 28 200'. So what I want is to change to a mopre correct datatype, DATETIME. Note, There is a very simple short cut to this by increasing from char(10) to char(11), but I want to change the datatype completely.This is a way I tried to fix the problem, but it is not optimal because there is data in the table from 2006, 2007, and 2008. Not only 2008. Any help would be appreciated.....CREATE TABLE #NEWtest (CompletedDate datetime)INSERT INTO #NEWtest (CompletedDate)SELECT CompletedDate + '8' FROM Test EXEC sp_rename '#NEWtest', 'Test' So, as you can see, this will work..the problem is it will affect the other years incorrectly. Even CompletedDates 2006 will be changed to 2008.
is there any other audit/history table where you can get the dateinserted valuie from? |
 |
|
|
Kurmanc
Yak Posting Veteran
92 Posts |
Posted - 2008-08-20 : 09:36:36
|
| visakh16.The only info I can get is CreatedDate, which is of datatype datetime. So thanks to this I can change the values correctly. Anyway, the thing is I want to know if there is any smarter way to achieve my wish.Thanks guys for your responses. I will now check the site VGuyz wrote. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-20 : 09:51:19
|
quote:
Originally posted by Kurmanc
visakh16.The only info I can get is CreatedDate, which is of datatype datetime. So thanks to this I can change the values correctly. Anyway, the thing is I want to know if there is any smarter way to achieve my wish.Thanks guys for your responses. I will now check the site VGuyz wrote.
But if you're not storing the value anywhere or dont have any field from which this can be derived (like days passed,day count,...) , then i dont think you've any way of retrieving it. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-08-21 : 03:41:30
|
quote:
Originally posted by Kurmanc
Hi,There is a table "Test" that has a column "CompeltedDate" with datatype char(10). I do not like this because it cuts the last number, like this 'Oct 28 200'. So what I want is to change to a mopre correct datatype, DATETIME. Note, There is a very simple short cut to this by increasing from char(10) to char(11), but I want to change the datatype completely.This is a way I tried to fix the problem, but it is not optimal because there is data in the table from 2006, 2007, and 2008. Not only 2008. Any help would be appreciated.....CREATE TABLE #NEWtest (CompletedDate datetime)INSERT INTO #NEWtest (CompletedDate)SELECT CompletedDate + '8' FROM Test EXEC sp_rename '#NEWtest', 'Test' So, as you can see, this will work..the problem is it will affect the other years incorrectly. Even CompletedDates 2006 will be changed to 2008.
How do you which value belongs to which year?MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
simple way to update datatype?