Conversion failed when converting date and/or time from character string.

  • I am trying to display to date fields into one field. the highlighted portion below is how I am trying to display the. data.

    if the patient had 2 procedures done I would like to display the 2 dates in the one date field.

    it is rare, but can happen.

    the first 2 highlighted parts are working fine. it is the 3rd highlighted area are the dates.

    I was able to display the procedure and code this way, not sure why I cant do this with the date fields.

    it is probably simple, but im stumped.

    SELECT DISTINCT

    T1.SessionID,

    T1.Neonate#,

    T1.[Total Neonates],

    T1.[Last Name],

    T1.[First Name],

    T1.MRN,

    T1.Account,

    T1.[Delivery Date],

    T1.[Delivery Type],

    T1.Anesthesia,

    T1.[Anesth. Code],

    T1.Complications,

    T1.Lacerations,

    T1.[PACU Code],

    --T1.PACU2,

    T1.CDM,

    T1.[Procedure],

    T1.Proc_Charge,

    T1.entrytime,

    T2.[PROCEDURE] AS II_PROCEDURE,

    case when t1.[PROCEDURE] = t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)

    then T1.[Procedure]

    when t1.[PROCEDURE] = t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) <> CAST(T2.[TIME] AS DATE)

    then (T2.[PROCEDURE]+'----'+ T2.CATEGORY )

    when T2.[PROCEDURE] is null and T1.[Procedure] is not null then T1.[Procedure]

    when T2.[PROCEDURE] ='' and T1.[Procedure] <>'' then T1.[Procedure]

    when T1.[PROCEDURE] is null and T2.[Procedure] is not null then (T2.[PROCEDURE]+'----'+ T2.CATEGORY )

    when T1.[PROCEDURE] ='' and T2.[Procedure]<>'' then (T2.[PROCEDURE]+'----'+ T2.CATEGORY )

    --when t1.[PROCEDURE] <> t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)

    --then T1.[PROCEDURE]+CHAR(10)+CHAR(13)+(T2.[PROCEDURE]+'----'+ T2.CATEGORY )

    when t1.[PROCEDURE] like '%tubal%' and t2.[PROCEDURE] like '%tubal%'

    and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE) then T1.[PROCEDURE]

    when t1.[PROCEDURE] <> t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)

    [highlight="#ffff11"]then T1.[PROCEDURE]+' -&- ' +(T2.[PROCEDURE]+'----'+ T2.CATEGORY )[/highlight]

    else null

    end as PROCEDURE_,

    case when t1.[PROCEDURE] = t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)

    then CAST(T1.Proc_Charge AS VARCHAR(255))

    when t1.[PROCEDURE] = t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) <> CAST(T2.[TIME] AS DATE)

    then CAST(T2.CDM AS VARCHAR(255))

    when T2.[PROCEDURE] is null and T1.[Procedure] is not null then CAST(T1.Proc_Charge AS VARCHAR(255))

    when T2.[PROCEDURE] ='' and T1.[Procedure] <>'' then CAST(T1.Proc_Charge AS VARCHAR(255))

    when T1.[PROCEDURE] is null and T2.[Procedure] is not null then CAST(T2.CDM AS VARCHAR(255))

    when T1.[PROCEDURE] ='' and T2.[Procedure]<>'' then CAST(T2.CDM AS VARCHAR(255))

    --when t1.[PROCEDURE] <> t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)

    --then T1.[PROCEDURE]+CHAR(10)+CHAR(13)+(T2.[PROCEDURE]+'----'+ T2.CATEGORY )

    when t1.[PROCEDURE] like '%tubal%' and t2.[PROCEDURE] like '%tubal%'

    and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE) then CAST(T1.Proc_Charge AS VARCHAR(255))

    when t1.[PROCEDURE] <> t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)

    then [highlight="#ffff11"]CAST(T1.Proc_Charge AS VARCHAR(255))+' -&- ' +CAST(T2.CDM AS VARCHAR(255))[/highlight]

    else null

    end as CDM_,

    case when t1.[PROCEDURE] = t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)

    then T1.entrytime

    when t1.[PROCEDURE] = t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) <> CAST(T2.[TIME] AS DATE)

    then T2.[TIME]

    when T2.[PROCEDURE] is null and T1.[Procedure] is not null then T1.entrytime

    when T2.[PROCEDURE] ='' and T1.[Procedure] <>'' then T1.entrytime

    when T1.[PROCEDURE] is null and T2.[Procedure] is not null then CAST(T2.[TIME] AS DATE)

    when T1.[PROCEDURE] ='' and T2.[Procedure]<>'' then CAST(T2.[TIME] AS DATE)

    --when t1.[PROCEDURE] <> t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)

    --then T1.[PROCEDURE]+CHAR(10)+CHAR(13)+(T2.[PROCEDURE]+'----'+ T2.CATEGORY )

    when t1.[PROCEDURE] like '%tubal%' and t2.[PROCEDURE] like '%tubal%'

    and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE) then T1.entrytime

    when t1.[PROCEDURE] <> t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)

    [highlight="#ffff11"]then CAST(T1.entrytime AS VARCHAR)+' -&- ' +CAST(T2.[TIME] AS VARCHAR)

    [/highlight]else null

    end as PROCEDURE_TIME,

    --26952

    T2.[TIME] AS II_DATE,

    T2.CATEGORY AS II_CATEGORY,

    T2.CDM AS II_CDM,

    T2.PACU AS II_PACU,

    T2.ANESTHESIA AS II_ANESTHESIA,

    T2.ANESTH_CHARGE AS II_ANESTH_CHARGE,

    T1.[Adhesive Barrier],

    T1.Manufacturer,

    T1.CatalogNumber,

    T1.LotNumber,

    T1.Qty,

    T1.[Implant Charge],

    T1.FacilityName

    FROM

    DBO.CHARGECAPTURE T1

    LEFT OUTER JOIN

    DBO.CHARGECAPTURE2 T2

    ON T1.SESSIONID = T2.SESSIONID

    WHERE T1.entrytime BETWEEN '01/01/2013' AND '08/20/2016'

    OR T2.[TIME] BETWEEN '01/01/2013' AND '08/20/2016'

    -- GETDATE()

    --AND T1.MRN =

    ORDER BY T1.SESSIONID

    thanks for the help, this forum is a great learning site!!!

  • when t1.[PROCEDURE] <> t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)

    then CAST(T1.entrytime AS VARCHAR)+' -&- ' +CAST(T2.[TIME] AS VARCHAR)

    Looks like you're comparing T1.[Delivery Date] to T2.[TIME] in the WHEN but then trying to concatenate T1.entrytime and T2.[TIME]...are you sure T1.entrytime is NOT NULL?

    _____________________________________________________________________
    - Nate

    @nate_hughes
  • thanks, but that is still generating the error message.

    if I remove the --&-- and just have below, it is not always correct.

    when t1.[PROCEDURE] <> t2.CATEGORY and CAST(T1.entrytime AS DATE) = CAST(T2.[TIME] AS DATE)

    then T1.entrytime +' ' +T2.[TIME]

    I get results once in a record 10/11/29 22:42

    how can I display the 2 date values in one field?

  • Going back to your original statement, if both columns are NOT NULL and CONVERTed to strings then you should be able to concatenate them.

    USE tempdb;

    DECLARE @Tmp TABLE (Date1 DATETIME, Date2 DATETIME);

    INSERT INTO @Tmp(Date1,Date2)

    VALUES

    (GETDATE(),DATEADD(HOUR,2,GETDATE()))

    ,(GETDATE(),NULL);

    SELECTISNULL(CAST(Date1 AS VARCHAR(50)),'') + '-&-' + ISNULL(CAST(Date2 AS VARCHAR(50)),'')

    FROM@Tmp;

    _____________________________________________________________________
    - Nate

    @nate_hughes
  • It's because of the return type of CASE.

    A CASE expression's return data type will be the data type with the highest precedence of all the possible result expressions. See https://msdn.microsoft.com/en-us/library/ms181765.aspx#Anchor_2

    Some of the result expressions are CAST as DATE, which seems to be the highest precedence data type in your potential result expressions. See https://msdn.microsoft.com/en-us/library/ms190309.aspx.

    The problem then is that with the result of the CASE expression being of data type DATE, the concatenation of two dates is not a valid DATE.

    If you really want to be able to concatenate two dates, then you'll need to make sure all result expressions are converted to varchar.

    Cheers!

    EDIT: Changed the link to the CASE documentation to go straight to the "Return Types" section.

  • All results of a CASE expression must be of compatible data types and the data type with the highest precedence determines the final data type. You return a mix of DATETIME data and VARCHAR data in your CASE expression, and, since DATETIME has a higher precedence than VARCHAR, all of your VARCHAR expressions are converted to DATETIME including 'Aug 30 2016 --&-- Sep 9 2016'. Since that's not a valid date/time, it returns an error.

    You need to make sure that you are only returning VARCHAR data in your CASE expression.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Once you get the data type precedence (stated above) figured out, you'll probably want to format both of those dates so they display in the same format. Whatever you do, don't use the new FORMAT function to do it because of known performance problems. You're better off to use DATEPART to build your string to return.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply