Conversion failed when converting datetime from character string.

Question

Conversion failed when converting datetime from character string.

Susan Laing

Say Hey Kid

Points: 664
More actions
October 22, 2007 at 8:57 am

#180592

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

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

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

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 1

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

Susan Laing Say Hey Kid Points: 664 More actions · Answer 2

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

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 3

I mean, in your WHERE clause

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

Susan Laing Say Hey Kid Points: 664 More actions · Answer 4

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

Susan

Susan Laing Say Hey Kid Points: 664 More actions · Answer 5

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,

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 6

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

Susan Laing Say Hey Kid Points: 664 More actions · Answer 7

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

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 8

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.

Susan Laing Say Hey Kid Points: 664 More actions · Answer 9

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

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 10

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.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

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)

Susan Laing Say Hey Kid Points: 664 More actions · Answer 12

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

jon-474332 SSC Eights! Points: 839 More actions · Answer 13

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..

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

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)