Hi All
I have an HL7 message coming in and am attempting to write it an MS SQL db.
The MS SQL db has the MSHMsgDtm defined as MSHMsgDtm(datetime,null)
The error I get back on the is the following: "Conversion failed when converting date and/or time from character string."
If I hard code a value in on the date such as '1998-12-25', it posts. I did this simply to make sure the rest of my statement was working.
I attempted CONVERT(DATETIME,@MSH.DateTimeOfMessage.Time,101) as well as many other variations. I'm not an SQL guru and am spending a great deal of time trying to figure out the correct syntax. Thanks for any assistance.
INSERT INTO HOSPITAL_Cutover_DFTs (MSHSendApp, MSHSendFac, MSHRecvApp, MSHRecvFac, MSHMsgDtm, MSHCtrlID, PIDIntID, PIDLastName, PIDFirstName, PIDVisitNo, FT1SetID, FT1TransID, FT1TransBatchID, FT1TransPostDtm, FT1TransType, FT1TransCode, FT1TransDesc, FT1TransDescAlt, FT1TransQty, FT1TransAmtExt, FT1TransAmtUnit, FT1DeptCode, FT1PatnLocn, FT1PatnType, FT1DiagCode, FT1PerfByCode, FT1ProcCode)
VALUES (
@MSH.SendingApplication.NamespaceID,
@MSH.SendingFacility.NamespaceID,
@MSH.ReceivingApplication.NamespaceID,
@MSH.ReceivingFacility.NamespaceID,
CONVERT(DATETIME,@MSH.DateTimeOfMessage.Time,101),
@MSH.MessageControlID,
@PID.InternalPatientID.IDNumber,
@PID.PatientName.FamilyName,
@PID.PatientName.GivenName,
@PID.PatientAccountNumber.IDNumber,
@FT1.SetID,
@FT1.TransactionID,
@FT1.TransactionBatchID,
'1998-12-25',
@FT1.TransactionType,
@FT1.TransactionCode.Identifier,
@FT1.TransactionDescription,
@FT1.TransactionAltDescription,
@FT1.TransactionQuantity,
@FT1.TransactionExtendedAmount.Price,
@FT1.TransactionUnitAmount.Price,
@FT1.DepartmentCode.Identifier,
@FT1.AssignedPatientLocation,
@FT1.PatientType,
@FT1.DiagnosisCode.Identifier,
@FT1.PerformedByCode,
@FT1.ProcedureCode.Identifier
)