Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Conversion failed when converting datetime from character string. Expand / Collapse
Author
Message
Posted Monday, October 22, 2007 8:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:04 PM
Points: 14, Visits: 182
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
Posted Monday, October 22, 2007 9:10 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:47 AM
Points: 1,800, Visits: 1,543
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
Posted Monday, October 22, 2007 9:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:04 PM
Points: 14, Visits: 182
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
Posted Monday, October 22, 2007 9:50 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:47 AM
Points: 1,800, Visits: 1,543
I mean, in your WHERE clause
DATEPART (year,CAST (importDate AS DATETIME)) = 2007
Post #413475
Posted Monday, October 22, 2007 10:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:04 PM
Points: 14, Visits: 182
Sorry, yes I've tried this and get the same message.

Susan
Post #413480
Posted Monday, October 22, 2007 10:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:04 PM
Points: 14, Visits: 182
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
Posted Monday, October 22, 2007 10:27 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:47 AM
Points: 1,800, Visits: 1,543
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
Posted Monday, October 22, 2007 11:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:04 PM
Points: 14, Visits: 182
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
Posted Monday, October 22, 2007 12:38 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:47 AM
Points: 1,800, Visits: 1,543
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
Posted Monday, October 22, 2007 3:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:04 PM
Points: 14, Visits: 182
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 »

Add to briefcase 12»»

Permissions Expand / Collapse