invalid length parameter on date cast

Question

Select CAST(SUBSTRING(actual_implementation_date, CHARINDEX(',', actual_implementation_date, 0)+2, CHARINDEX('((', actual_implementation_date, 0)-(CHARINDEX(',', actual_implementation_date, 0)+2)) AS datetime) AS DATECASTValue from TABLE1

Hello everyone! This is the query i'm using to cast a string into a date after stripping the day of the week and timezone from the beginning and end of the string.

Right now it can only work 1 row at a time. If i do not put a parameter in where it will only return one row, i get this error "Invalid length parameter passed to the LEFT or SUBSTRING function." Any ideas?

 

Answer 1

can you please post sample data and desired output?

---

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 2

Hi,

What is the data type for actual_implementation_date? As you said it returns one row and throws an error, i think there is some problem while doing the operation on the second row. Can you post the actual_implementation_date values may be top 10 rows?

Regards
Satheesh

Answer 3

Hi,

I just re-looked at the query you posted and it looks like the datatype is varchar, so most probable case is you may have a malformed string in the column (my guess is the value is in the second of your output).

Regards
Satheesh

Answer 4

This is the format of the date as it is stored.

Tuesday, May 01, 2012  3:00 PM((UTC-05:00) Eastern Time (US & Canada))

In the query, if I select * from the table, it will return all rows. If I replace * with ticket_number, then it gives me the error message. If I add a where clause witht the ticket number, it returns the date correctly. I would like to see the results as "Ticket_number, 6/25/2012" for all tickets so I can then do some date range caculations with another date that is stored as datetime. Does that make sense?

select
 CAST(SUBSTRING(actual_implementation_date, CHARINDEX(',', actual_implementation_date, 0)+2, CHARINDEX('((', actual_implementation_date, 0)-(CHARINDEX(',', actual_implementation_date, 0)+2)) AS datetime) AS DATECASTValue,
* from TABLE1

---

bonnie

Answer 5

Is that possible just concatenate  cast( ticket_number as varchar(20))+ cast(varchar(20),actual_implementation_date,112))

from tbl...?

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 6

I need ticket number and actual_implementation_date to be in different columns. Ultimately, I need to use the actual_implementation_date to determine number of days to close the ticket. Closed_date is another column in the table that is already formatted to datetime. I need to subtract closed_date from actual_implementation_date to get the days_to_close. That is why I need that actual_implementation_date as a datetime.

---

bonnie

Answer 7

Please post sample data + desired result. CREATE TABLE...+INSERT INTO...

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 8

The desired result is to have the x_actual_imp_date_text be formatted the same way ProcessStarted and ProcessEnded are.

process_id	DatePosted	DisplayName	ProcessStarted	ProcessEnded	x_actual_imp_date_text	x_change_type
RTI-006414	07-May-12	Atha, Daniel AH/US/EXT	01-May-12	07-May-12	Wednesday, May 02, 2012  12:00 PM((UTC-05:00) Eastern Time (US & Canada))	Implementation Only
RTI-006415	21-May-12	Atha, Daniel AH/US/EXT	01-May-12	21-May-12	Saturday, May 12, 2012  11:00 AM((UTC-05:00) Eastern Time (US & Canada))	Implementation Only
RTI-006416	08-May-12	Parkins, Adolph AH/US	01-May-12	08-May-12	Tuesday, May 01, 2012  3:00 PM((UTC-05:00) Eastern Time (US & Canada))	Implementation Only
RTI-006417	03-May-12	Guillen, Carl AH/US	01-May-12	03-May-12	Tuesday, May 01, 2012  2:00 PM((UTC-05:00) Eastern Time (US & Canada))	Implementation Only
RTI-006418	01-May-12		01-May-12	01-May-12	Tuesday, May 01, 2012  4:00 PM((UTC-05:00) Eastern Time (US & Canada))	Implementation Only
RTI-006419	02-May-12	Miao, Lin AH/US	01-May-12	02-May-12	Wednesday, May 02, 2012  5:00 AM((UTC-05:00) Eastern Time (US & Canada))	Implementation Only
RTI-006420	20-May-12	Ng, May AH/SG	02-May-12	20-May-12	Saturday, May 19, 2012  4:00 AM((UTC+08:00) Kuala Lumpur, Singapore)	Implementation Only
RTI-006421	04-May-12	CAILLE, Herve AH/FR	02-May-12	04-May-12	Friday, May 04, 2012  8:00 AM((UTC-05:00) Eastern Time (US & Canada))	Implementation Only
RTI-006422	04-May-12	GAILLY, Martin AH/FR	02-May-12	04-May-12	Wednesday, May 02, 2012  10:15 AM((UTC+01:00) Brussels, Copenhagen, Madrid, Paris)	Implementation Only

---

bonnie

Answer 9

As has been pointed out, the problem is that your data contains something that will not CAST as DATETIME.  One approach is to check for the non-date and substitute something else for it.  For example:

Select 
   CASE 
     WHEN ISDATE(CAST(SUBSTRING(actual_implementation_date, CHARINDEX(',', actual_implementation_date, 0)+2, 
          CHARINDEX('((', actual_implementation_date, 0)-(CHARINDEX(',', actual_implementation_date, 0)+2)) AS VARCHAR(100))) = 1 THEN 
          
              CAST(SUBSTRING(actual_implementation_date, CHARINDEX(',', actual_implementation_date, 0)+2, 
              CHARINDEX('((', actual_implementation_date, 0)-(CHARINDEX(',', actual_implementation_date, 0)+2)) AS datetime)
      ELSE NULL
    END  AS DATECASTValue
FROM ...

In this case, every row should either return the actual_implementation_date (according to the parsing rules that you provide) or it will return a NULL.  NULL means that the string was not a valid date.  You can, of course, substitute some other default date in that case.

RLF

Answer 10

Thank you, Russell. I did finally figure out that was what was happening. So, if I wanted to return a string like "NoDate" to make the query continue to run past the invalid date fields, what would I need to do? I know there are cases where a date has been manually entered (for tickets that run into a problem in the workflow) but they are not in the same format as the "cast" statement and they are not in the same format every time. I have tried specifically excluding these ticket numbers in the query, but it doesn't work.

RTI-005007	12/18/2011 23:10
RTI-005046	3 Jan 2012 09:41 AM

Select result,process_id, CASE WHEN ISDATE(CAST(SUBSTRING(x_actual_imp_date_text, CHARINDEX(',', x_actual_imp_date_text, 0)+2, CHARINDEX('((', x_actual_imp_date_text, 0)-(CHARINDEX(',', x_actual_imp_date_text, 0)+2)) AS VARCHAR(100))) = 1 THEN CAST(SUBSTRING(x_actual_imp_date_text, CHARINDEX(',', x_actual_imp_date_text, 0)+2, CHARINDEX('((', x_actual_imp_date_text, 0)-(CHARINDEX(',', x_actual_imp_date_text, 0)+2)) AS datetime) ELSE NULL END AS DATECASTValue FROM ServiceDeskChangeManagement, ReportProcess where process_id is not null and x_actual_imp_date_text is not null and ReportProcess.SessionID = ServiceDeskChangeManagement.session_id and process_id <> 'rti-004525' and process_id <> 'rti-005007' and process_id <> 'rti-005046' and process_id <> 'rti-005068' and process_id <> 'rti-005090' and process_id <> 'rti-005272' and process_id <> 'rti-005286' and process_id <> 'rti-005364' and process_id <> 'rti-005608' and process_id <> 'rti-005711' and process_id <> 'rti-006401' and ProcessStarted > '01/01/2012' order by process_id	x_actual_imp_date_text

---

bonnie

Answer 11

Bonnie,

The first problem is that you apparently are dealing with character strings for dates instead of DATE or DATETIME dates.  That is why your formatting is all over the place and is causing you grief. 

If you can get that column converted to a DATE column or a DATETIME2, then the input to the column will error when someone enters an invalid date.  And one of the date types will not allow 'NoDate' to be stored.

If you are using one of the DATE datatype, then for "NoDate" the answer is NULL, although you can select a real date, such as 1800/01/01 to serve as "NoDate".  You have to manage the logic of course and turn the output into "NoDate" (if that is what you want) on the select.

Obviously excluding ticket numbers is an impossible task.  That is why my previous post used a CASE statement to avoid allowing unusable date strings from hanging up the process. 

So: (1) Change the data types and institute rules to avoid the problem date strings, and (2) Determine how you want to identify missing/unknown dates.

RLF