?
Solved

Inserting Date field date not compatible

Posted on 2014-01-04
4
Medium Priority
?
315 Views
Last Modified: 2014-01-04
I am creating a Server Agent job to insert new data into a teble from a Select statement.

I am getting the error:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Here is the structure of the table:
create table tbl_Midnight_Stay_Admissions
([Account Number] varchar(15),
[Date of Admission] date,
[Patient Name] varchar(74),
[Attending Physician] varchar(75))
;

Here is the Insert Query:

nsert into [livedb].[PATIENTSMC\gpowers].[tbl_Midnight_Stay_Admissions]
SELECT     livedb.dbo.BarVisits.AccountNumber AS [Account Number], livedb.dbo.BarVisits.Name AS [Patient Name], CONVERT(varchar(10), DATEADD(DD, DATEDIFF(DD, 0,
                      livedb.dbo.BarVisits.AdmitDateTime), 0), 101) AS [Date of Admission], livedb.dbo.BarVisitFinancialData2.AttendProviderName AS [Attending Physician]
FROM         livedb.dbo.BarVisits LEFT OUTER JOIN
                      livedb.dbo.BarVisitFinancialData2 ON livedb.dbo.BarVisits.VisitID = livedb.dbo.BarVisitFinancialData2.VisitID LEFT OUTER JOIN
                      livedb.dbo.DMisInsurance ON livedb.dbo.BarVisits.PrimaryInsuranceID = livedb.dbo.DMisInsurance.InsuranceID
WHERE     (livedb.dbo.BarVisits.AdmitDateTime > CONVERT(DATETIME, '2013-12-31 00:00:00', 102)) AND (livedb.dbo.BarVisits.InpatientOrOutpatient = 'I') AND
                      (livedb.dbo.DMisInsurance.DefaultFinancialClassID IN ('MCR', 'MCR HMO')) AND (CONVERT(varchar(10), DATEADD(DD, DATEDIFF(DD, 0,
                      livedb.dbo.BarVisits.AdmitDateTime), 0), 101) >= DATEADD(DD, DATEDIFF(DD, 0, GETDATE()) - 3, 0))

What can I do to correct the Convert statement to create the [Date of Admission] field?

thanks

Glen
0
Comment
Question by:GPSPOW
  • 2
4 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 39756141
SELECT     livedb.dbo.BarVisits.AccountNumber AS [Account Number], livedb.dbo.BarVisits.Name AS [Patient Name],
DATEADD(DD, DATEDIFF(DD, 0,getdate()), 0) AS [Date of Admission],
livedb.dbo.BarVisitFinancialData2.AttendProviderName AS [Attending Physician]
FROM....

will work
0
 

Author Comment

by:GPSPOW
ID: 39756219
Here is modified code:

Insert into [livedb].[PATIENTSMC\gpowers].[tbl_Midnight_Stay_Admissions]
SELECT     livedb.dbo.BarVisits.AccountNumber AS [Account Number], livedb.dbo.BarVisits.Name AS [Patient Name],  
                      livedb.dbo.BarVisits.AdmitDateTime, livedb.dbo.BarVisitFinancialData2.AttendProviderName AS [Attending Physician]
FROM         livedb.dbo.BarVisits LEFT OUTER JOIN
                      livedb.dbo.BarVisitFinancialData2 ON livedb.dbo.BarVisits.VisitID = livedb.dbo.BarVisitFinancialData2.VisitID LEFT OUTER JOIN
                      livedb.dbo.DMisInsurance ON livedb.dbo.BarVisits.PrimaryInsuranceID = livedb.dbo.DMisInsurance.InsuranceID
WHERE      (livedb.dbo.BarVisits.InpatientOrOutpatient = 'I') AND
                      (livedb.dbo.DMisInsurance.DefaultFinancialClassID IN ('MCR', 'MCR HMO')) AND
                      livedb.dbo.BarVisits.AdmitDateTime >= DATEADD(DD, DATEDIFF(DD, 0, GETDATE()) - 3, 0)

I modified the target table structure to have a field AdmitDateTime with a type of datetime.

I am still getting the same error:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.


Glen
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 39756264
see this article to know how to handle date/time (and conversion) correctly:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html

and you need to check out which field is indeed giving the issue, then which "value" is giving the issue.
0
 

Author Closing Comment

by:GPSPOW
ID: 39756341
thanks

the article gave me insight on how to fix my problem.

Glen
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

579 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