Solved

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

Posted on 2011-03-03
2
1,461 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 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now