Validate, Scrub, and Format String Date

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

Transact-SQL (2008)

Validate, Scrub, and Format String Date

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2012-01-12 : 13:21:23

I have been trying in vain to create a function that takes a string column populated with a date formatted like '20120101' and validate that it really IS a date and not some other characters and then, and only then, put it into another column formatted as '2012-01-01'. So it begins as a VARCHAR and ends as a VARCHAR but must be a valid date. I would like to let it return NULL if it is blank, but '1900-01-01' if it is otherwise invalid. I have the following code that returns a DATE and it works fine to validate a date and returns a date, but I want another to return it as a formatted string as mentioned above:

CREATE FUNCTION [dbo].[fnScrubDate](@strDateToScrub VARCHAR(10))
RETURNS DATE
AS
BEGIN
SET @strDateToScrub = dbo.fnScrubText(@strDateToScrub,'?','')
SET @strDateToScrub = dbo.fnScrubText(@strDateToScrub,' ','')
RETURN CASE 
   WHEN ISNULL(@strDateToScrub,'') = '' THEN NULL
   WHEN ISDATE(@strDateToScrub) = 1 THEN CAST(@strDateToScrub AS DATE) ELSE '19000101' END
END

P.S. - fnScrubText just removes question marks and spaces before doing anything else.

Duane

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-12 : 13:30:00

CAST(CAST(@strDateToScrub AS DATE) AS datetime) ELSE CAST('19000101' AS Datetime)

???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2012-01-12 : 13:56:19

quote:
Originally posted by X002548

CAST(CAST(@strDateToScrub AS DATE) AS datetime) ELSE CAST('19000101' AS Datetime)

Thank you for the reply. Gave me some ideas and I came up with this:

CREATE FUNCTION [dbo].[fnScrubTextDate](@strDateToScrub VARCHAR(10))
RETURNS VARCHAR(10)
AS
BEGIN
SET @strDateToScrub = dbo.fnScrubText(@strDateToScrub,'?','')
SET @strDateToScrub = dbo.fnScrubText(@strDateToScrub,' ','')
SET @strDateToScrub = RTRIM(LTRIM(@strDateToScrub))

RETURN CASE
	WHEN ISNULL(@strDateToScrub,'') = '' THEN NULL
	WHEN ISDATE(@strDateToScrub) = 1 THEN  CAST(CAST(@strDateToScrub AS DATE) AS VARCHAR) ELSE CAST('1900-01-01' AS VARCHAR) END
END

The only thing I would have wanted more is to be able to pass in the format character that I wanted in the result. For example, instead of the dash '-', maybe the slash '/'. That way, if I come up with more like this, I wouldn't have to write a whole new function differing only in the format character.
This does work, however, and returns the correct results for valid values, blank values, and invalid values.

Duane

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-12 : 14:02:12

That wouldn't matter if it is a valid date

change it to varchar(25)


DECLARE @strDateToScrub varchar(25);

SELECT CASE
   WHEN ISNULL(@strDateToScrub,'') = '' THEN NULL
   WHEN ISDATE(@strDateToScrub) = 1 THEN CAST(@strDateToScrub AS DATE) ELSE '19000101' END
   
SET @strDateToScrub = ''

SELECT CASE
   WHEN ISNULL(@strDateToScrub,'') = '' THEN NULL
   WHEN ISDATE(@strDateToScrub) = 1 THEN CAST(@strDateToScrub AS DATE) ELSE '19000101' END
      
SET @strDateToScrub = '20010911'

SELECT CASE
   WHEN ISNULL(@strDateToScrub,'') = '' THEN NULL
   WHEN ISDATE(@strDateToScrub) = 1 THEN CAST(@strDateToScrub AS DATE) ELSE '19000101' END
      
SET @strDateToScrub = '9/11/2001'

SELECT CASE
   WHEN ISNULL(@strDateToScrub,'') = '' THEN NULL
   WHEN ISDATE(@strDateToScrub) = 1 THEN CAST(@strDateToScrub AS DATE) ELSE '19000101' END
      
SET @strDateToScrub = '9-11-2001'

SELECT CASE
   WHEN ISNULL(@strDateToScrub,'') = '' THEN NULL
   WHEN ISDATE(@strDateToScrub) = 1 THEN CAST(@strDateToScrub AS DATE) ELSE '19000101' END
 
      
SET @strDateToScrub = '2001-09-11'

SELECT CASE
   WHEN ISNULL(@strDateToScrub,'') = '' THEN NULL
   WHEN ISDATE(@strDateToScrub) = 1 THEN CAST(@strDateToScrub AS DATE) ELSE '19000101' END
 
      
SET @strDateToScrub = 'XXX'

SELECT CASE
   WHEN ISNULL(@strDateToScrub,'') = '' THEN NULL
   WHEN ISDATE(@strDateToScrub) = 1 THEN CAST(@strDateToScrub AS DATE) ELSE '19000101' END

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-12 : 15:10:09

Why one would store dates as string, forrmatted or not, is beyond me. But, if you want to do that you might take a look at the CONVERT function. It takes a STYLE parameter that you can use to specify how you want the date string formatted. To get it into the YYYY-MM-DD format there are several styles that will do that (120, 121, 126, 127, etc).

http://msdn.microsoft.com/en-us/library/ms187928.aspx

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2012-01-12 : 15:55:11

Thank you both for the input. I am not authorized to make changes in formatting or data types. I am upgrading a data mart which has a lot of issues like this and I already am having to rewrite much of it, but not things like this. And there is too much of it, anyway.

All the different formats above are interesting as well. I am sure I will be able to use this info going forward.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources