| Author |
Topic |
|
Mhackel
Starting Member
12 Posts |
 Posted - 2010-04-22 : 10:57:00
|
| I have to update some date in a secondary table. The field I am pulling from Detail Value is a varchar 8000 and I need to go to Creation which is a datetime. I keep getting the Msg 241, Level 16, State 1, Line 2 Conversion failed when converting date and/or time from character string error. Here is my query:update [Item_Support]set [Item_Support].Creation = Convert(datetime,[Data_UDFDetail].DetailValue,21)FROM [Data_UDFDetail] INNER JOIN [Item_Support] ON [Item_Support].SupportID = [Data_UDFDetail].ItemIdwhere SupportID in (SELECT [ItemId] FROM [TdF_Master].[dbo].[Data_UDFDetail]where ControlId = '140'and DetailValue != '' ) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-22 : 11:04:35
|
| how are date values stored in varchar column? in what format?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-22 : 11:04:49
|
First you need to know the actual format of the date in your varchar column.Then you can look in BOL for CONVERT on how to format this back to datetime.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-22 : 11:05:37
|
The old rocker was too slow again.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Mhackel
Starting Member
12 Posts |
Posted - 2010-04-22 : 14:29:24
|
| The data is in the correct format I need for my field update. I just need to update the creation date. It worked when I copied the files with data in test, but I got this error in prod. The data is yyyy-mm-dd hh:mm:ss:msc looks like datetime format 21. This is why i was tryin to do this to convert it: Convert(datetime,Data_UDFDetail].DetailValue,21). |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-22 : 15:00:41
|
Ok, then maybe you have some bad formatted data in prod.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Mhackel
Starting Member
12 Posts |
Posted - 2010-04-22 : 15:21:12
|
| I copied the exact data into my test environment. So I don't think that I have bad data. I also created the test table exactly off the of the prod table. That is why I was confused I even needed to convert the Detail Value field at all because it worked perfectly in test. Here is a new thought - Since I have all my data in my test environment exactly as I need it would it just be easier to move that data into prod. It already is in a datetime format. The two databases are on the same server and instance just different databases. Just when I tried this the query was having a problem seeing both databases with my update statement. This is a one time thing. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-23 : 02:44:13
|
| are both the databases' language settings same? it can cause problems as interpretation of date values changes across languages------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Mhackel
Starting Member
12 Posts |
Posted - 2010-04-23 : 10:32:26
|
| Yes, the language schemas are the same. I initally did an insert into with the prod data to the test data to test with. |
|
|
|
Mhackel
Starting Member
12 Posts |
Posted - 2010-04-26 : 12:02:21
|
| I couldn't conquer this so I went a different route. I created a copy of the table of my Item.Support prod table and named it Test.Support. I just brought over the two fields that I needed. I was able to convert the varchar field into a datetime without a problem. I was then able to update my production table from the Test.Support table that was already in the datetime format. I am not sure what happened, but I finally go the results I needed. I am glad this was a one time only project. |
|
|
|
|
Update varchar to datetime