Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-03
2
Medium Priority
?
1,655 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
2 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Integration Management Part 2
Screencast - Getting to Know the Pipeline

877 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