Conversion failed when converting datetime from character string.

  • I have written the following query to extract data splitting a text string into separate fields. This query works fine and returns results.

    WITH a (company_id, user_id, project_id, customer_id, action, importdate, groupid, accountnumber, amount, notes, created, updated) AS

    (

    select

    company_id,

    user_id,

    project_id,

    customer_id,

    action,

    CAST(SUBSTRING(params, PATINDEX('%[ImportDate=%', params)+13, 19) AS datetime) AS ImportDate,

    SUBSTRING(params, PATINDEX('%GroupID=%', params)+8, 1) AS GroupID,

    SUBSTRING(params, PATINDEX('%AccountNo=%', params)+10, 7) AS AccountNumber,

    CAST(SUBSTRING(substring(params, 0, len(params)), PATINDEX('%Amount=%', params)+7, 10) AS decimal(18,2)) AS Amount,

    notes,

    created,

    updated

    from

    database.dbo.tablename

    where

    customer_id is not null and

    PATINDEX('%GroupID=%', params) <> 0 and

    PATINDEX('%ImportDate=%', params) <> 0 and

    PATINDEX('%AccountNo=%', params) <> 0 and

    PATINDEX('%Amount=%', params) <> 0

    )

    select

    company_id,

    user_id,

    project_id,

    customer_id,

    action,

    importdate,

    DATEPART(year, importdate) AS importyear,

    DATEPART(month, importdate) AS importmonth,

    groupid,

    accountnumber,

    amount,

    notes,

    created,

    updated

    from

    a

    The problem arises when I try to do a WHERE on either the importyear or importmonth field.

    WITH a (company_id, user_id, project_id, customer_id, action, importdate, groupid, accountnumber, amount, notes, created, updated) AS

    (

    select

    company_id,

    user_id,

    project_id,

    customer_id,

    action,

    CAST(SUBSTRING(params, PATINDEX('%[ImportDate=%', params)+13, 19) AS datetime) AS ImportDate,

    SUBSTRING(params, PATINDEX('%GroupID=%', params)+8, 1) AS GroupID,

    SUBSTRING(params, PATINDEX('%AccountNo=%', params)+10, 7) AS AccountNumber,

    CAST(SUBSTRING(substring(params, 0, len(params)), PATINDEX('%Amount=%', params)+7, 10) AS decimal(18,2)) AS Amount,

    notes,

    created,

    updated

    from

    database.dbo.tablename

    where

    customer_id is not null and

    PATINDEX('%GroupID=%', params) <> 0 and

    PATINDEX('%ImportDate=%', params) <> 0 and

    PATINDEX('%AccountNo=%', params) <> 0 and

    PATINDEX('%Amount=%', params) <> 0

    )

    select

    company_id,

    user_id,

    project_id,

    customer_id,

    action,

    importdate,

    DATEPART(year, importdate) AS importyear,

    DATEPART(month, importdate) AS importmonth,

    groupid,

    accountnumber,

    amount,

    notes,

    created,

    updated

    from

    a

    where DATEPART(year, importdate) = 2007

    I get

    Conversion failed when converting datetime from character string.

    but when I use ISDATE() to validate the importyear it returns nothing indicating that the actual importdate is a valid date. Can anyone help please?

    Thanks

  • Have u tried using Cast or Convert to convert your Varchar Date to Datetime and then using the function u are using now?

  • yes, I use cast in the innermost query then use datepart in the outer part to get the year and month. If I try to filter by the date, the year or the month in the where clause I get this error.

    I actually use this query to create a view and when looking at the view the columns are datetime for the importdate and int for the year and month. I can also select from the view with no problem. Also when I use ISDATE(importdate) on the view it comes back successful. The only time I get an error is when I try to do a WHERE importyear = 2007 or WHERE importmonth = 10

    I'm sure there must be an easy explanation here, but I just cant see it.

    Best regards

    Susan

  • I mean, in your WHERE clause

    DATEPART (year,CAST (importDate AS DATETIME)) = 2007

  • Sorry, yes I've tried this and get the same message.

    Susan

  • Here is an example of the source data

    id,user_id, project_id, customer_id, company_id, action, params, notes,

    32352, NULL, 12345, 23456, 1, Insert,

    [ImportDate=Jun 30 2007 12:00AM][GroupID=1][AccountNo=ABC123][Amount=100.00], NULL,

  • Do u think there is a possibility u might be having a date that is not between this range:

    January 1 1753 to year Dec 31 9999

  • thats what I wondered, but when I do a select * from view where ISDATE(importdate) <> 1, it comes back with no records. Should this not catch anything outwith this date range?

    Best regards

  • No,

    ISDATE is different.

    You can use this ias an example

    DECLARE @datestring varchar(8)

    SET @datestring = '12/21/1098'

    SELECT ISDATE(@datestring)

    Above will still print out 1 because its an valid date. but still out of range for DATETIME.

  • Hi

    I've gone through and checked the dates and all are between 2007-06-30 and 2007-10-10 and appear to be in the correct format

    THanks for the suggestion

    Best regards

  • Hi,

    Option 1

    I am sorry u had to go through all the dates. How many total dates do u have? Can you run this query for me

    SELECT importdate FROM

    WHERE

    CAST(substring(importdate,8,4) AS integer) < 1754

    OR

    CAST(substring(importdate,8,4) AS integer) > 9998

    Option 2

    The DTS wizard can also help catch bad dates, when u try to transfer your table into some other temp table with DATETIME as the column instead of VARCHAR column, the DTS wizard will fail pointing out on which row or record it failed.

  • Look again... you're not getting what you think you're getting... run this small demo to see why...

    [font="Courier New"]DECLARE @params VARCHAR(100)

    SET @params = 'abcdefghij[ImportDate=12345678901234567890'

    SELECT SUBSTRING(@Params,PATINDEX('%[ImportDate=%', @params)+13,19)[/font]

    ...did you figure it out?

    Square brackets have a special meaning in LIKE and PATINDEX... you must "escape" the special characters to use them. One way is to put a SET of square brackets around them. Like this...

    [font="Courier New"]

    DECLARE @params VARCHAR(100)

    SET @params = 'abcdefghij[ImportDate=12345678901234567890'

    SELECT SUBSTRING(@Params,PATINDEX('%[[]ImportDate=%', @params)+13,19)[/font]

    ...but even that fails because of the +13 because you forgot to subtract the first character it found... run the code above and see...

    this is the correct code (not including your conversion to datetime)...

    [font="Courier New"]DECLARE @params VARCHAR(100)

    SET @params = 'abcdefghij[ImportDate=12345678901234567890'

    SELECT SUBSTRING(@Params,PATINDEX('%[[]ImportDate=%', @params)+12,19)[/font]

    The "+" number value right after the PATINDEX() must always be one less than the string you are looking for....

    You'll need to repair the other PATINDEXes, as well.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • It was suggested on another forum that I do this in the select statement of the innermost query and it works perfect.

    CAST(

    CASE WHEN ISDATE(SUBSTRING(params, PATINDEX('%[ImportDate=%', params)+13, 19) ) = 1

    THEN SUBSTRING(params, PATINDEX('%[ImportDate=%', params)+13, 19)

    ELSE NULL

    END

    AS DATETIME)

    AS ImportDate,

    Thanks everyone for your help

  • Hi there, have you tried convert()?? me only conversant in simple sql:hehe:

    WHERE YEAR(CONVERT(DATETIME, importdate, 103)) = 2007

    103 = DD/MM/YYYY format change to suit your date time zone accordingly 😉

    hope it helps..

  • Susan Laing (10/23/2007)


    It was suggested on another forum that I do this in the select statement of the innermost query and it works perfect.

    CAST(

    CASE WHEN ISDATE(SUBSTRING(params, PATINDEX('%[ImportDate=%', params)+13, 19) ) = 1

    THEN SUBSTRING(params, PATINDEX('%[ImportDate=%', params)+13, 19)

    ELSE NULL

    END

    AS DATETIME)

    AS ImportDate,

    Thanks everyone for your help

    Go back and look at my previous post... it's not perfect... it tricks you into thinking that...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 18 total)

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