Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2011
    Posts
    6

    Answered: :S Conversion failed when converting datetime from character string.

    HI All

    I've got problem been a week still didn't find any solutions of it..

    I'm using SQL server 2005.

    how to solve this error when insert there is an error message :
    Conversion failed when converting datetime from character string.

    here is my table create Table syntax:
    CREATE TABLE [dbo].[THRMEMPMEDICALHISTORY_TEST](
    [MEDICALHISTORY_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [EMP_ID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [START_DATE] [datetime] NULL,
    [END_DATE] [datetime] NULL,
    [HOSPITAL_CODE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DOCTOR_CODE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DISEASE] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [LAB_CODE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [LAB_CONCLUSION] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [MEDICALRESULT_CODE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [MEDICAL_CATEGORY] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [MEDICAL_DOCFILE] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CURRENCY_ID] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [FEE] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DISABILITYCODE] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [MEDICALRESULTTO_CODE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ACTIONNOTE] [varchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [MEDICINENOTE] [varchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [LOCATION] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TYPEOFEXPENSES] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DOCTORHOSPITAL] [int] NULL,
    [TXT_DOCTORHOSPITAL] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ACCOUNT_ID] [int] NULL,
    [PROJECT_ID] [int] NULL,
    [CONTACT_PERSON] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CONTACT_POSITION] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PAID_STATUS] [int] NULL,
    [PAID_DATE] [datetime] NULL,
    [IS_LEADER] [int] NULL,
    [EMPFAMILY_ID] [int] NULL,
    [REQUESTFOR_EMPID] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ITEMDETAIL] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [LSTDAYTREAT] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [LOAN_NO] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [REIM_CODE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [REIMBURSEFOR] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ISALLOW] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [LOAN_AMMOUNT] [int] NULL,
    [LOAN_CODE] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [APPROVED_LOAN_AMOUNT] [int] NULL,
    [LETTER] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DISEASTYPE] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [APROVED_GROSSUP] [float] NULL,
    [IS_VOUCHER] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PAYLEVELCODE] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [POSITION] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ORGANIZATIONLEVEL] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [JOBSTATUS] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [JOBGRADE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CREATEDBY] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CREATEDDATE] [datetime] NULL,
    [UPDATEDBY] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [UPDATEDDATE] [datetime] NULL,
    [CONSEQUENCE] [varchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [COMPLAINTNOTE] [varchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [FISIK] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [RONTGENT_THORAX] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [URINE] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DARAH] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HATI] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [GLUKOSA] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Cholinesterase] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]



    and this is insert SQL

    INSERT INTO THRMEMPMEDICALHISTORY_TEST (HOSPITAL_CODE, DOCTOR_CODE,EMP_ID,START_DATE,END_DATE,FISIK,RONTG ENT_THORAX,URINE,DARAH,HATI,GLUKOSA,LAB_CONCLUSION ) VALUES (
    'BIO','BIODR','RTK070045','OverWeight, Penurunan Visus Mata kanan dan kiri 20/25, Calculus (+), Sisa Akar (+)',CONVERT(datetime, '2011-05-04 00:00:00'),CONVERT(datetime, '5/28/2011 00:00:00'),'Dalam batas Normal','Dalam batas normal','Peningkatan LED','Dalam batas normal','Dalam batas normal','Diet rendah kalori, olahraga lebih teratur Cek visus setahun sekali Jaga kebersihan gigi dan mulut Jika ada keluhan konsultasi ke dokter')


    What is wrong I'v tried convert cast on date still resulting on same error...

    please help guys...

    Thankyou In Advance

  2. Best Answer
    Posted by Wim

    "There are two different datetime representations in your code, both YYYY-MM-DD and MM/DD/YYYY. Give the correct type as and extra parameter to the CONVERT function.

    Try:
    Code:
    CONVERT(datetime, '2011-05-04 00:00:00', 121),
    CONVERT(datetime, '5/28/2011 00:00:00', 101)
    "


  3. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    There are two different datetime representations in your code, both YYYY-MM-DD and MM/DD/YYYY. Give the correct type as and extra parameter to the CONVERT function.

    Try:
    Code:
    CONVERT(datetime, '2011-05-04 00:00:00', 121),
    CONVERT(datetime, '5/28/2011 00:00:00', 101)
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #3
    Join Date
    Aug 2011
    Posts
    6
    Quote Originally Posted by Wim View Post
    There are two different datetime representations in your code, both YYYY-MM-DD and MM/DD/YYYY. Give the correct type as and extra parameter to the CONVERT function.

    Try:
    Code:
    CONVERT(datetime, '2011-05-04 00:00:00', 121),
    CONVERT(datetime, '5/28/2011 00:00:00', 101)
    thank you for the hint Wim..
    but its still error with the same error message..

  5. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    When I wrote the column names next to the values, I noticed a michmatch.
    Code:
    VALUES (
    'BIO',	--HOSPITAL_CODE, 
    'BIODR',	-- DOCTOR_CODE,
    'RTK070045',	-- EMP_ID,
    'OverWeight, Penurunan Visus Mata kanan dan kiri 20/25, Calculus (+), Sisa Akar (+)',	--START_DATE,
    CONVERT(datetime, '2011-05-04 00:00:00', 121),	-- END_DATE,
    CONVERT(datetime, '5/28/2011 00:00:00', 101),	-- FISIK,
    'Dalam batas Normal',	--RONTGENT_THORAX,
    'Dalam batas normal',	--URINE,
    'Peningkatan LED',	--DARAH,
    'Dalam batas normal',	--HATI,
    'Dalam batas normal',	-- GLUKOSA,
    'Diet rendah kalori, olahraga lebih teratur Cek visus setahun sekali Jaga kebersihan gigi dan mulut Jika ada keluhan konsultasi ke dokter' -- LAB_CONCLUSION ) 
    )
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #5
    Join Date
    Aug 2011
    Posts
    6
    Quote Originally Posted by Wim View Post
    When I wrote the column names next to the values, I noticed a michmatch.
    Code:
    VALUES (
    'BIO',	--HOSPITAL_CODE, 
    'BIODR',	-- DOCTOR_CODE,
    'RTK070045',	-- EMP_ID,
    'OverWeight, Penurunan Visus Mata kanan dan kiri 20/25, Calculus (+), Sisa Akar (+)',	--START_DATE,
    CONVERT(datetime, '2011-05-04 00:00:00', 121),	-- END_DATE,
    CONVERT(datetime, '5/28/2011 00:00:00', 101),	-- FISIK,
    'Dalam batas Normal',	--RONTGENT_THORAX,
    'Dalam batas normal',	--URINE,
    'Peningkatan LED',	--DARAH,
    'Dalam batas normal',	--HATI,
    'Dalam batas normal',	-- GLUKOSA,
    'Diet rendah kalori, olahraga lebih teratur Cek visus setahun sekali Jaga kebersihan gigi dan mulut Jika ada keluhan konsultasi ke dokter' -- LAB_CONCLUSION ) 
    )
    well thanks but

    try this its still resulting.. error convert this is the hardest db problem oh my sql server...

    INSERT INTO THRMEMPMEDICALHISTORY_TEST
    (HOSPITAL_CODE
    , DOCTOR_CODE
    ,EMP_ID
    ,START_DATE
    ,END_DATE
    ,FISIK
    ,RONTGENT_THORAX
    ,URINE
    ,DARAH,HATI
    ,GLUKOSA
    ,LAB_CONCLUSION) VALUES

    ('BIO'
    ,'BIODR'
    ,'RTK070045'
    ,'OverWeight, Penurunan Visus Mata kanan dan kiri 20/25, Calculus (+), Sisa Akar (+)'
    ,CONVERT(datetime, '2011-05-04 00:00:00', 121)
    ,CONVERT(datetime, '2011-05-28 00:00:00', 121)
    ,'Dalam batas Normal'
    ,'Dalam batas normal'
    ,'Peningkatan LED'
    ,'Dalam batas normal'
    ,'Dalam batas normal'
    ,'Diet rendah kalori, olahraga lebih teratur Cek visus setahun sekali Jaga kebersihan gigi dan mulut Jika ada keluhan konsultasi ke dokter')

  7. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The list with the column names and the values is still not synchronised.

    You try to insert the value
    ,'OverWeight, Penurunan Visus Mata kanan dan kiri 20/25, Calculus (+), Sisa Akar (+)'

    into the START_DATE column, of DATE(TIME) type. That will never work.

    As long as you don't fix that you will keep getting those conversion errors.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #7
    Join Date
    Aug 2011
    Posts
    6
    Quote Originally Posted by Wim View Post
    The list with the column names and the values is still not synchronised.

    You try to insert the value
    ,'OverWeight, Penurunan Visus Mata kanan dan kiri 20/25, Calculus (+), Sisa Akar (+)'

    into the START_DATE column, of DATE(TIME) type. That will never work.

    As long as you don't fix that you will keep getting those conversion errors.

    ok I get it now problem solved many thanks and I'm sorry I did'n't realy thoroughly observe and synchronize fields and values to be equal..

    once again thank you very much for support...
    Last edited by jcjaychou; 09-13-11 at 00:42. Reason: typos

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •