Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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,604 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 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

704 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