Solved

How to avoid the error "Conversion failed when converting character string to smalldatetime data type"?

Posted on 2011-03-03
2
1,561 Views
Last Modified: 2012-08-13
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);
0
Comment
Question by:zimmer9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 500 total points
ID: 35028609
Re-check your insert/values statement.

Currently, its trying to insert "Access" into ProdDate - looks like you got unsynced somewhere
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 35028643
insert into tAllApplications
(appID,Application, ApplTypeID,  serverMain,serverAlt, DeptID, sponsor, Description,SystemContact, Location, backEnd, frequencyOfUse, dataSrc, dataDep)
values ('325', 'ReportsDB', 'Distributed','N/A', 'N/A','Retail Banking', 'Nalini Ramlachan', 'Reporting System','J_Braffman', 'Ni Ram; C Ng', 'Access', 'Daily', 'BRS', '1');
0

Featured Post

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question