Conversion failed error when inserting date values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Conversion failed error when inserting date values

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-07-28 : 10:56:19

Please stick with me on this one - I'm enclosing more data than usual in the hope somebody might see something that's not right...

My ASP.NET application allows the user to enter a number of dates, which it then converts to dates in the VB.NET format, checks they're valid, converts them into SQL date format, and then appends into a long string. So (assuming en-GB date format)...


30/06/2010
18/5/11
29/5/10

...would be read, checked, reformatted and passed to SQL as an NVARCHAR(MAX) parameter like so...


@dates = '2010-06-30T00:00:00|2011-05-18T00:00:00|2010-05-10T00:00:00|'

SQL then puts them into a table variable like so:


DECLARE @separator_position INT
DECLARE @array_value nvarchar(max)
SET @separator_position = NULL
SET @array_value = NULL
DECLARE @datesTable TABLE(ID INT IDENTITY(1,1), dateAttained DATETIME)

WHILE patindex('%' + @separator + '%', @dates) <> 0
	BEGIN
		SELECT @separator_position =  patindex('%' + @separator + '%' , @dates)
		SELECT @array_value = LEFT(@dates, @separator_position - 1)
		INSERT INTO @datesTable(dateAttained) VALUES (CONVERT(DATETIME, @array_value))
		SELECT @dates = stuff(@dates, 1, @separator_position, '')
	END

And finally I select these dates from the table variable to my proper database table.

Somewhere along this logic a user is reporting an exception:

Conversion failed when converting datetime from character string.

Can anyone see any immediate floors in my logic? The user is going to send me a list of dates to try out, but I am trying to pre-empt this so I don't have to enter all their data to find the problem. This code has worked for years so I don't understand why now it might be failing...

Here's hoping...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-28 : 12:52:22

You can see what is failing by replacing the convert with an isdate function. What I mean is something like this:


WHILE patindex('%' + @separator + '%', @dates) <> 0
	BEGIN
		SELECT @separator_position =  patindex('%' + @separator + '%' , @dates)
		SELECT @array_value = LEFT(@dates, @separator_position - 1)
		IF (ISDATE(@array_value) = 0) PRINT @array_value;
		--INSERT INTO @datesTable(dateAttained) VALUES (CONVERT(DATETIME, @array_value))
		SELECT @dates = stuff(@dates, 1, @separator_position, '')
	END

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-07-29 : 02:28:58

Okay thanks. I wasn't aware that T-SQL also included the ISDATE function (I've been using it in ASP.NET to test dates, which is why I'm so confused why this error is happening).

I think I would use RAISERROR if ISDATE returns 0, which would then ROLLBACK the tran (which is done outside of the scope of my posted code) and raise that exception up to ASP.NET to provide help to the user. Is it possible to place the value of @array_value into a RAISERROR message?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 02:33:42

yep you can do it. it will be like

RAISERROR ('Invalid date value %s',16,1,CAST(@array_value AS varchar(20)));

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-07-29 : 02:57:21

Hi visakh16

Thanks - that's exactly what I need in order for ASP.NET to handle the error gracefully.

The only problem is that when I apply your code, I get this error: Incorrect syntax near 'CAST'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-29 : 06:54:36

Change it to


declare @somevar varchar(20);
set @somevar = CAST(@array_value AS varchar(20));
RAISERROR ('Invalid date value %s',16,1,@somevar);

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-29 : 07:37:52

I also want to mention that while ISDATE is useful, it may not entirely eliminate the problems you ran into. For example, run this code - you will see that the last statement fails even though ISDATE returns true in all three cases.

SELECT ISDATE('20110701');
SELECT CONVERT(DATETIME,'20110701',101);

SELECT ISDATE(40723);
SELECT CONVERT(DATETIME,40723,101);

SELECT ISDATE(20110701);
SELECT CONVERT(DATETIME,20110701, 101);

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-07-29 : 08:15:34

Hmm that's interesting. I haven't actually used the Style parameter on the CONVERT function. As the dates are always passed as yyyy-mm-ddThh:mm:ss I think 126 is the correct style to specify...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources