Checking variable data type for CONVERT / CAST

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 2000 Forums

Transact-SQL (2000)

Checking variable data type for CONVERT / CAST

Author

Topic

abissus
Starting Member

3 Posts

Posted - 2004-07-26 : 16:18:12

I'm trying to create a procedure or function that will determine whether or not an input parameter of type varchar is suitable for converting to another datatype. Is there a way, short of writing a bunch of string manipulation routines, to trap the error generated by a CAST or CONVERT to check the integrity of the data before having to insert it into a column of a specific datatype?

I considered using the ISDATE() and ISNUMERIC() functions already, but they are not useful for seeing if a date or number is in the range specified by a specific datatype. For instance, in the case of a smalldatetime datatype, a user could key in a field for birthday with the year 1873 or something like that. This value would be return a 1 in ISDATE and be suitable for storing in a larger datetime column, but invalid to store in a smalldatetime column.

Unfortunately, preventing the error on the front-end system where the user types in the data is not an option.

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-26 : 16:35:40

So....make your own


USE Northwind
GO

CREATE FUNCTION udf_IsSmallDate(@str varchar(25))
RETURNS int
AS  
BEGIN
	DECLARE @rc int
	SELECT @rc = 0
	 IF ISDATE(@str) = 1 
	AND DATEDIFF(d,@str,'1/1/1900') > 0
	AND DATEDIFF(d,'6/6/2079',@str) > 0
		SELECT @rc = 1
	RETURN @rc
  END
GO


SELECT dbo.udf_IsSmallDate('1/1/1800')
SELECT dbo.udf_IsSmallDate(GetDate())
SELECT dbo.udf_IsSmallDate('12/31/9999')
GO

DROP FUNCTION udf_IsSmallDate
GO

Brett

8-)

n/a
deleted

35 Posts

Posted - 2004-07-26 : 16:45:07

You can trap an error and rollback the transaction if you need to.

This would be inside your proc for inserting


begin transaction
insert into Table1
values('1111', '05/05/05', 1)

if @@error <> 0
	begin
		rollback transaction
		print 'failed'
		select @@error
	end
else
	begin
		commit
		print 'success'
	end

You will then need to write code to handle different error messages different ways.

Another way to approach this is to create user defined data types to specifically meet your needs. Then make those the input parameters on both the procedure and the table. This will then return errors before the procedure even kicks off.

HTH
Paul

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-27 : 10:03:39

OK, hold on...that didn't work....

SELECT ISDATE(GetDate())
SELECT DATEDIFF(d,GetDate(),'1/1/1900')
SELECT DATEDIFF(d,'6/6/2079',GetDate())

The conditions are all true....somethings wrong....

Hold on....

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-27 : 10:17:09

I like this better (mostly because this one works...why the other doesn't...

)


USE Northwind
GO

CREATE FUNCTION udf_IsSmallDate(@str varchar(25))
RETURNS int
AS  
BEGIN
	DECLARE @rc int
	SELECT @rc = 0
	 IF ISDATE(@str) <> 1
	   RETURN @rc
 
	 IF DATEDIFF(d,'1/1/1900',@str) > 0
	AND DATEDIFF(d,'6/6/2079',@str) < 0

		SELECT @rc = 1
	RETURN @rc
  END
GO


SELECT dbo.udf_IsSmallDate('1/1/1800')
SELECT dbo.udf_IsSmallDate(GetDate())
SELECT dbo.udf_IsSmallDate('12/31/9999')
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources