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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-11-23 : 07:04:32
|
| Maryann writes "I have a table where chg_date field in character format(char(10) (yyyymmdd)). I select distinct on this table into a new table(2).The chg_date field in table (2) is in datetime format. When I try to copy table 1 into table 2 I am receiving the error:The conversion of a char data typw to a datetime data type resulted in a out-of-range datetime value. The statement has been terminated.I have even tried to do a convert & Cast on the field:convert(char(10),chg_date,120)cast(chg_date as datetime)I receive the same error with both also.Is this able to be done? " |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-11-23 : 07:14:16
|
| I guess you need to increase the character len.. Convert(Char(20),Chg_date,120)..????or if you just createing the new table you can try out somthing like this Select * into Table1 From Table2??Sucess Comes to those who Believe in Beauty of their Dream.. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-23 : 07:48:51
|
| Insert into Target table(columns) Select OtherColumns,Cast(dateField as DateTime) from SourceTableMadhivananFailing to plan is Planning to fail |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-11-23 : 07:51:37
|
| You have one or more invalid dates in chg_dateYou can find the invalid dates with:select * from MyTable where isdate(chg_date) <> 1CODO ERGO SUM |
|
|
|
|
|
|
convert char field to datetime field when creating a new tab