Validating VARCHAR will convert to DATETIME

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)

Validating VARCHAR will convert to DATETIME

Author

Topic

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2008-01-09 : 16:28:57

Hello fellow database peoples!

I am seeking a Scalar UDF that accepts a VARCHAR field and returns a bit value indicating whether or not the incoming VARCHAR will convert to a datetime datatype. It seems like this has been done before, so I am hoping one of you knowledgeable folks will be able to provide.

As an added bonus, it would be nice if it automatically accepted a string in the format YYYYMMDDHHMMSS (i.e. 20071108193200). Regardless, that could be easily be converted with another function.

Thanks for the help!

- Shane

EDIT: TG wins the bonus! Kudos to TG! (Finally, a situation to use STUFF in)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-09 : 16:31:17

will the "out of the box" isDate('<string>') do it for you?

EDIT:
Do I get the bonus???

declare @d char(14)
set @d = '20071108193200'
select isDate(stuff(stuff(stuff(stuff(stuff(@d, 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 7, 0, '-'), 5, 0, '-'))

Be One with the Optimizer
TG

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2008-01-09 : 16:49:03

Indeed it will. I thank you, good sir.

I think I've overlooked this built-in function due to the lack of built-in validation functions for other specific data types (like INT). ISNUMERIC really just doesn't cut the mustard in a lot of scenarios.

Speaking of which, I've implemented some custom functions to specifically validate INT, SMALLINT, & DECIMAl and I'd be curious what the best ways to do that are. I'm pretty happy with my functions, but I'm always interested in a better solution. If there is a good reference out there, I'd appreciate a link.

Thanks,

- Shane

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-09 : 17:01:00

You may find some issues with isDate as well. The best way to insure correct type is to, of course, TYPE the variables or columns that hold the data. I realize that is not always possible depending on existing app/DB design and the source of the data.

Check the "script library" forum. Also post what you've got, I bet if anyone has a better way they will respond.

Be One with the Optimizer
TG

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2008-01-09 : 17:12:48

Thanks for all your help TG. My principal requirement is to be able to use this function on a set within SQL Server. I briefly messed around with the TRY...CATCH functionality with a CONVERT in the TRY but it isn't suited for a UDF (unless I missed something).

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-09 : 17:24:38

>>My principal requirement is to be able to use this function on a set...
Do you mean a set of data (multiple rows), or do you mean when assigning a value? (ie: SET @v = <udf()>)

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-09 : 17:34:12

>>but it isn't suited for a UDF (unless I missed something).
yep, can't use try/catch in a udf.

Have you condidered simply setting a value to the typed value in a try/catch then performing your 'invalid' logic in the catch.

EDIT:
this is what I meant


declare @d datetime
begin try
	set @d = 'bogus date'
end try
begin catch
	print 'invalid date'
end catch

Be One with the Optimizer
TG

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2008-01-09 : 18:24:13

I can't use that method as a set-based operation... it requires a loop of some type. Ultimately, I want to get the PK of all records that fail validation from a given set of VARCHARs.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-09 : 18:42:44

Can't you just extract the invalid PK's out by doing something like this?

Select [PK]
Into #nondates
From [Table]
Where isDate(stuff(stuff(stuff(stuff(stuff([PK], 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 7, 0, '-'), 5, 0, '-')) = 0

Poor planning on your part does not constitute an emergency on my part.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2008-01-09 : 20:09:55

Of course. I mean that I can't use TG's second proposed method in a set-based operation:

quote:

declare @d datetime
begin try
set @d = 'bogus date'
end try
begin catch
print 'invalid date'
end catch

Sorry for any miscommunication; my initial post was already solved with the isDate(stuff...) method. I was simply exploring other options in the following posts.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-09 : 20:45:55

NO, but you can use a set based operation to achieve a similar end, perhaps using Case...When to evaluate the field..

I guess even reading through, I am not seeing the context properly of what you are asking...

Poor planning on your part does not constitute an emergency on my part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-10 : 01:33:36

Handle ISDATE() with care
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2008-01-10 : 02:31:46

Fair enough madhivanan. Luckily, I am verifying the length at exactly 14 and using the ISDATE(Stuff()...) method above.

dataguru1971: I am importing large amounts of data many times a minute into a SQL Server. The data originates from custom code and streams over to my database server. My chosen ETL framework pumps it into tables defined with VARCHAR fields and then traverses a stored procedure hierarchy where each field is validated. The first set of steps ensure all required elements are present and elements have the right datatype. After it passes the basic smell test, it's pumped into strongly typed fields in another related table where more steps enforce certain business rules. All the while, I am taging each failed record with a unique ID that tells me why it failed (Incidentally, the tag also links it to the batch and caries other metadata about the load like start & end date/time, result, records processed, duration, etc..). When the data has passed all tests, it is progressed to the full relational structures in the ODS. Additionally, all data associated with the load is moved off asynchronously into holding areas where I can report on it, send notifications, or automatically resubmit it for processing (in the case of timing related failures).

Ultimately, I wanted a solution like TG gave me that allows me to use a fast, set based operation to validate my VARCHAR data before it's INSERTED into a strongly typed field.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2008-01-10 : 15:56:47

As TG suggested, I posted my INT, SMALLINT, TINYINT and DECIMAL validation functions on the script forum. Suggestions & improvements welcome:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=95322

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources