How to avoid the error "Conversion failed when converting character string to smalldatetime data type"?
Posted on 2011-03-03
I am trying to insert a record into a SQL Server table using Microsoft SQL Server 2005.
How do I insert a record with a field titled ProdDate as a NULL value into a table when this field is defined as type smalldatetime?
I tried using a value of Null for the ProdDate field as per the following SQL statement and I get an error as follows:
Msg 295, Level 16, State 3, Line 12
Conversion failed when converting character string to smalldatetime data type.
SET IDENTITY_INSERT tAllApplications ON
insert into tAllApplications
(appID, LDRPSID, Application, ApplTypeID, NewApplTypeID, serverMain,
serverAlt, DeptID, sponsor, Description, RecoveryTime, SupportDeptLevel1,
FSSupportID, SystemContact, Location, SLA, [SLA Alternative], backEnd, ProdDate,
decomDate, frequencyOfUse, dataSrc, dataDep, active, jp)
values ('325', Null, 'ReportsDB', 'Distributed', Null, 'N/A', 'N/A',
'Retail Banking', 'Nalini Ramlachan', 'Reporting System',
Null, Null, Null, 'J_Braffman', 'Ni Ram; C Ng',
Null, Null, Null, 'Access', Null, Null, 'Daily', 'BRS', '1', Null);