Extracting a date from a varchar field

Question

Extracting a date from a varchar field

Rasmus Remmer Bielidt

SSCommitted

Points: 1767
More actions
August 31, 2011 at 6:17 am

#256490

We're using an old system that keeps track of each produced item by a batchseriesno field. Unfortunately, there apparently is no validation of input on the database (it's in the application, which has been changed many times over).
Now I'm in a situation where I need to extract a date field from a 16 character string and I've tried giving it a go with a scalar UDF.
CREATE FUNCTION [dbo].[Batchseriesdate] (@batchseriesno varchar)
RETURNS date
AS
BEGIN
DECLARE @cleardate date
--DECLARE @year varchar(2);
--DECLARE @dayofyear varchar(3);
--SET @cleardate = CAST('19000101' AS DATE)
SET @cleardate =
CASE ISNUMERIC(SUBSTRING(@batchseriesno,3,2)+ISNUMERIC(SUBSTRING(@batchseriesno,5,3))
WHEN 0 THEN CAST('19000101' AS DATE)
WHEN 1 THEN CAST('19000101' AS DATE)
ELSE DATEADD(dd,CAST(SUBSTRING(@batchseriesno,5,3) AS INT)-1,DATEADD(yy,cast(SUBSTRING(@batchseriesno,3,2) AS INT)-1900,'19000101'))
END
RETURN(@cleardate)
END
GO
It's my newbie attempt to validate that the batchnumber contains valid date (two-digit) on the third and fourth position and a valid dayofyear on the fifth through seventh position. But SQL server doesn't like the CASE..WHEN and returns the following error.
Msg 156, Level 15, State 1, Procedure Batchseriesdate, Line 14
Incorrect syntax near the keyword 'WHEN'.
Msg 102, Level 15, State 1, Procedure Batchseriesdate, Line 19
Incorrect syntax near 'END'.
As you may be able to see from the commented out declared variables, I've thought about validating each part of the string individually but havent hit anything useful.
Edit: Looking at it, the line with ELSE would return illegal values as well I suppose, but I think I can fix that later.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

How about posting a dozen or so of these values?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

How about this?

(I've used SQL Server 2008 syntax, since that's the forum you posted in)

CREATE FUNCTION [dbo].[Batchseriesdate] (@batchseriesno VARCHAR(16))

RETURNS DATE

AS

BEGIN

DECLARE @cleardate DATE

SET @batchseriesno = RTRIM(LTRIM(@batchseriesno))

SELECT @cleardate = CONVERT(DATE,@batchseriesno)

FROM (SELECT CASE WHEN SUBSTRING(@batchseriesno,1,4) <= '1753'

THEN 0

ELSE MAX(PATINDEX([goodDates],@batchseriesno)) END AS valid

FROM (VALUES ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),

('[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'),

('[0-9][0-9][0-9][0-9]/[0-9][0-9]/[0-9][0-9]')) AS pat([goodDates]) ) a

WHERE valid = 1

RETURN(@cleardate)

END

GO

I tested it against the following sample data: -

----First, lets build some test data

DECLARE @TABLE AS TABLE (funkydate VARCHAR(16))

INSERT INTO @TABLE (funkydate)

SELECT '20100101' UNION ALL

SELECT '2010-02-01' UNION ALL

SELECT '2010/03/01' UNION ALL

SELECT '201A0401' UNION ALL

SELECT 'A0100601' UNION ALL

SELECT '20100101 abc'

--Now use our new function

SELECT dbo.Batchseriesdate(funkydate), funkydate

FROM @TABLE

Which returned the first three as dates, the rest as NULL.

Is that what you're after?

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

scotthill142 Grasshopper Points: 11 More actions · Answer 3

This is what i was searching for a month now, thanks Cadavre - you are a savior!:-)

Cadavre (8/31/2011)
How about this?
(I've used SQL Server 2008 syntax, since that's the forum you posted in)
CREATE FUNCTION [dbo].[Batchseriesdate] (@batchseriesno VARCHAR(16))
RETURNS DATE
AS
BEGIN
DECLARE @cleardate DATE
SET @batchseriesno = RTRIM(LTRIM(@batchseriesno))
SELECT @cleardate = CONVERT(DATE,@batchseriesno)
FROM (SELECT CASE WHEN SUBSTRING(@batchseriesno,1,4) <= '1753'
THEN 0
ELSE MAX(PATINDEX([goodDates],@batchseriesno)) END AS valid
FROM (VALUES ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
('[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'),
('[0-9][0-9][0-9][0-9]/[0-9][0-9]/[0-9][0-9]')) AS pat([goodDates]) ) a
WHERE valid = 1
RETURN(@cleardate)
END
GO
I tested it against the following sample data: -
----First, lets build some test data
DECLARE @TABLE AS TABLE (funkydate VARCHAR(16))
INSERT INTO @TABLE (funkydate)
SELECT '20100101' UNION ALL
SELECT '2010-02-01' UNION ALL
SELECT '2010/03/01' UNION ALL
SELECT '201A0401' UNION ALL
SELECT 'A0100601' UNION ALL
SELECT '20100101 abc'
--Now use our new function
SELECT dbo.Batchseriesdate(funkydate), funkydate
FROM @TABLE
Which returned the first three as dates, the rest as NULL.
Is that what you're after?

www.google.com