CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
user1741845
user1741845

Reputation: 1

SQL server 2008: Conversion of varchar to datetime failed

I have problems with the following code:

USE [PCIPNY];

insert into [dbo].[test_Drug_Medication_1]
(
       [MedicationID] ,[ClinicID] ,[PatientID] ,[MyDrugID] ,[NDC] ,[DoctorID] ,[DrugID] ,[DrugFullDesc] ,[Generic_Name]
      ,[Print_Name] ,[Manufacture] ,[Inactive_Date] ,[Strength] ,[Units] ,[Pkg_Size] ,[Pkg_Type] ,[Route] ,[Take]
      ,[Frequency] ,[Duration] ,[Qualifier] ,[Quantity] ,[Refill] ,[Note] ,[DAW] ,[CreateDate] ,[CreateBy]
      ,[ModifyBy],[IsControl] ,[UserDefine] ,[scheduleLevel] ,[BeginDate] ,[EndDate] ,[Active] ,[sente]
      ,[OnCologyCheckStatus] ,[OnCologyCheckStatus1] ,[OnCologyCheckStatus2] ,[SIG] ,[Printed] ,[ICDCode] ,[SendeFaxed] ,[DosageForm] ,[GPI]
      ,[IsBrand] ,[IsGeneric] ,[GenericAndBrand] ,[SigUnit] ,[IsDrug] ,[Status]
)

SELECT
      [MedicationID] ,[ClinicID] ,[PatientID] ,[MyDrugID] ,[NDC] ,[DoctorID] ,[DrugID] ,[DrugFullDesc] ,[Generic_Name]
      ,[Print_Name] ,[Manufacture] ,[Inactive_Date] ,[Strength] ,[Units] ,[Pkg_Size] ,[Pkg_Type] ,[Route] ,[Take]
      ,[Frequency] ,[Duration] ,[Qualifier] ,[Quantity] ,[Refill] ,[Note] ,[DAW] ,[CreateDate] ,[CreateBy]
      ,[ModifyBy] ,[IsControl] ,[UserDefine] ,[scheduleLevel] ,[BeginDate] ,[EndDate] ,[Active] ,[sente]
      ,[OnCologyCheckStatus] ,[OnCologyCheckStatus1] ,[OnCologyCheckStatus2] ,[SIG] ,[Printed] ,[ICDCode] ,[SendeFaxed] ,[DosageForm] ,[GPI]
      ,[IsBrand] ,[IsGeneric] ,[GenericAndBrand] ,[SigUnit] ,[IsDrug] ,[Status]

  FROM [ec14].[dbo].[Drug_Medication]
    where 1=1
      and clinicid in (select clinicid from [dbo].[clinic] where org_db = 'ec14' and ClinicID=1234);

I got this error:

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

I found out that the error is in the column begindate.

The column begindate in ec04(origin location) is charvar(16) datatype.
The column begindate in PCIPNY(destination location) is datetime datatype.

Is there any solution to make this work?

Upvotes: 0

Views: 1342

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

In the SELECT clause from source, change

BeginDate

To

CASE WHEN ISDATE(BeginDate) = 1 then CAST(BeginDate AS DATETIME) END

It is a case of not setting the DMY/MDY format correct? You can try changing SET DATEFORMAT. Or are all the dates (in varchar) in a specific format that can actually be converted? If so you can use CONVERT with a format specifier.

CONVERT(datetime, BeginDate, 103)

Upvotes: 1

dgarbacz
dgarbacz

Reputation: 962

Make sure that the date is in the correct format, or that the columns didn't get shifted. I've had this error before when the order of the columns being inserted is wrong. It's possible the data attempting to go into the date field is not the date and another field.

Upvotes: 0

Doon
Doon

Reputation: 20232

try casting the field you select from ec14..

, cast(begindate as datetime) ,

Upvotes: 0

Related Questions