Solved

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

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

Title # Comments Views Activity
OPENDATASOURCE 8 42
SQL Query with Sum and Detail rows 2 56
SSRS: Why is Visual Studio stripping these properties? 2 23
Need help in debugging a UDF results 7 28
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

820 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