Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Conversion failed when converting datetime from character string.


Conversion failed when converting datetime from character string.

Author
Message
Susan Laing
Susan Laing
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 188
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
Mayank Khatri
Mayank Khatri
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1811 Visits: 1694
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
Susan Laing
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 188
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
Mayank Khatri
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1811 Visits: 1694
I mean, in your WHERE clause
DATEPART (year,CAST (importDate AS DATETIME)) = 2007
Susan Laing
Susan Laing
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 188
Sorry, yes I've tried this and get the same message.

Susan
Susan Laing
Susan Laing
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 188
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
Mayank Khatri
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1811 Visits: 1694
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
Susan Laing
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 188
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
Mayank Khatri
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1811 Visits: 1694
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
Susan Laing
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 188
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search