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 |
|
ryanmcalister
Starting Member
1 Post |
 Posted - 2006-07-11 : 09:50:15
|
| I have 2 tables (baddate and gooddate) and added a ‘orderdate’ column to each. In the baddate I added a value of 00-XXX-00 to the orderdate column. There is no data in the gooddate table. I’m using the following statement to try to insert the 00-XXX-00 value into the gooddate table but it will always fail with the “Conversion failed when converting datetime from character string" error.INSERT INTO gooddate([orderdate])selectCONVERT(VARCHAR(50), CAST(orderdate AS DATETIME), 101)[orderdate]from baddateIf I change the value in the baddate table to something like 03-DEC-06 the INSERT statement works perfectly and brings the value in as 12/03/2006.Anyone have anyway to get the 00-XXX-00 values into a table as NULLs? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-07-11 : 09:57:16
|
| It is always GOOD to use Proper datatype DateTime to both GOOD and BAD dates. Otherwise it is BAD for both dates though the attempts are GOOD. Dont use varchar datatype to store dates. Use Datetime datatype and send the date in YYYYMMDD format from the client application or use stored procedure with datatime parameters which is GOOD. Otherwise everything will be BADMadhivananFailing to plan is Planning to fail |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-07-11 : 09:57:16
|
use nullif(orderdate, '00-XXX-00')
KH |
|
|
|
|
|
|
Conversion failed when converting datetime from ch