Conversion failed when converting datetime from character string.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Conversion failed when converting datetime...

19 posts, Page 1 of 2

1 2 »»

Conversion failed when converting datetime from character string.

Rate Topic

Display Mode

Topic Options

Author

Message

Susan Laing

Posted Monday, October 22, 2007 8:57 AM

Grasshopper

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 14, Visits: 146

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

Post #413452

Mayank Khatri

Posted Monday, October 22, 2007 9:10 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:43 AM
Points: 1,776, Visits: 1,442

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

Post #413456

Susan Laing

Posted Monday, October 22, 2007 9:17 AM

Grasshopper

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 14, Visits: 146

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

Post #413459

Mayank Khatri

Posted Monday, October 22, 2007 9:50 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:43 AM
Points: 1,776, Visits: 1,442

I mean, in your WHERE clause
DATEPART (year,CAST (importDate AS DATETIME)) = 2007

Post #413475

Susan Laing

Posted Monday, October 22, 2007 10:05 AM

Grasshopper

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 14, Visits: 146

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

Susan

Post #413480

Susan Laing

Posted Monday, October 22, 2007 10:14 AM

Grasshopper

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 14, Visits: 146

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,

Post #413484

Mayank Khatri

Posted Monday, October 22, 2007 10:27 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:43 AM
Points: 1,776, Visits: 1,442

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

Post #413487

Susan Laing

Posted Monday, October 22, 2007 11:19 AM

Grasshopper

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 14, Visits: 146

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

Post #413503

Mayank Khatri

Posted Monday, October 22, 2007 12:38 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:43 AM
Points: 1,776, Visits: 1,442

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.

Post #413530

Susan Laing

Posted Monday, October 22, 2007 3:28 PM

Grasshopper

Group: General Forum Members
Last Login: Today @ 1:24 AM
Points: 14, Visits: 146

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

Post #413596

« Prev Topic | Next Topic »

19 posts, Page 1 of 2

1 2 »»

Permissions