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 |
|
drman
Starting Member
38 Posts |
 Posted - 2009-01-20 : 10:37:56
|
| I have a table that contains a date field (smalldatetime). I have been asked to clear this field so there is no automated activity when particular dates come up. I do not want to lose the dates as they will be restored later, so I was going to move them to an existing varchar field so the original dates are available for viewing to the client. When I run the following command on a specific record as follows, it adds the date as (MD:yyyy-mm-dd hh:mm:ss) to the description field and clears out the original date field.update InfoTablesetDescription = ltrim(Description) + ' (MD:' + Convert(varchar, MoveDate, 20) + ')', MoveDate = NULLwhere UniqueId = '8850228'To restore the date from the varchar field back to the date field and clear the info out of the description field, I run and it works fine... update InfoTablesetMoveDate = cast( substring( Description, charindex('(MD:', description)+4, 19) as smalldatetime ), Description = substring( Description, 1, charindex('(MD:', description)-1) where Uniqueid = '8850228' If I try and do a global change to a set of records using the following command, it fails:update InfoTablesetDescription = ltrim(Description) + ' (RD:' + Convert(varchar, MoveDate, 20) + ')'where CustomerNo = '191' and (CustomerDate < '01/01/2009' and Movedate is Not NULL)I will follow up with the exact error message.Thanks in advance.Yak |
|
|
bjoerns
Posting Yak Master
154 Posts |
Posted - 2009-01-20 : 10:52:00
|
| Better add another smalldatetime column. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-20 : 11:35:42
|
| if its a date value, why use varchar? this will make calculations for dates complex. always try to use proper datatype for fields |
|
|
|
drman
Starting Member
38 Posts |
Posted - 2009-01-20 : 11:38:38
|
| I was adding it to an existing Varchar field to display on a website without having to actually redo on the site. I believe I figured it out. Lengths were the actual issue.thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-20 : 11:44:52
|
| but then it would be a real pain if you want to use date values for any manipulation |
|
|
|
drman
Starting Member
38 Posts |
Posted - 2009-01-20 : 13:07:32
|
| They move right back... All is great!!! thanks |
|
|
|
|
|
|
|
|
Update command???