SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Conversion failed error when inserting date values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

R
Constraint Violating Yak Guru

United Kingdom
328 Posts

Posted - 07/28/2011 :  10:56:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 07/28/2011 :  12:52:22  Show Profile  Reply with Quote
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
Go to Top of Page

R
Constraint Violating Yak Guru

United Kingdom
328 Posts

Posted - 07/29/2011 :  02:28:58  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/29/2011 :  02:33:42  Show Profile  Reply with Quote
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/

Go to Top of Page

R
Constraint Violating Yak Guru

United Kingdom
328 Posts

Posted - 07/29/2011 :  02:57:21  Show Profile  Reply with Quote
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'
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 07/29/2011 :  06:54:36  Show Profile  Reply with Quote
Change it to

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

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 07/29/2011 :  07:37:52  Show Profile  Reply with Quote
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);
Go to Top of Page

R
Constraint Violating Yak Guru

United Kingdom
328 Posts

Posted - 07/29/2011 :  08:15:34  Show Profile  Reply with Quote
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...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000