ISDATE() And converting

Question

Post reply

ISDATE() And converting

danielfountain

SSCarpal Tunnel

Points: 4229
More actions
March 26, 2012 at 2:10 am

#253094

Hey all,
I have a database that is unfortunately not under my control. There is a certain varchar that may or may not have a date in it.
Here is my some example data and the query i am using to convert all valid dates:
SET DATEFORMAT DMY
CREATE TABLE #temptable
(
DateString VARCHAR(100)
)
INSERT INTO #temptable
VALUES ( '26/5/2011' )
INSERT INTO #temptable
VALUES ( '13/5/2011' )
INSERT INTO #temptable
VALUES ( '09/11/1' )
INSERT INTO #temptable
VALUES ( '29/7/2011' )
INSERT INTO #temptable
VALUES ( '14.09.10' )
INSERT INTO #temptable
VALUES ( '9/6/2010' )
INSERT INTO #temptable
VALUES ( '21/2/2011' )
INSERT INTO #temptable
VALUES ( '26/5/2011' )
INSERT INTO #temptable
VALUES ( '21/2/2011' )
INSERT INTO #temptable
VALUES ( '09/11/1' )
INSERT INTO #temptable
VALUES ( 'ABC123' )
INSERT INTO #temptable
VALUES ( 'Some Text' )
SELECT CONVERT(DATETIME, RIGHT('00' + CAST(DAY(DateString) AS VARCHAR(2)), 2)
+ '/' + RIGHT('00' + CAST(MONTH(DateString) AS VARCHAR(2)), 2) + '/'
+ CAST(YEAR(DateString) AS VARCHAR(4)), 103)
FROM #temptable
WHERE ISDATE(DateString) = 1
This all works fine (as it seems the convert must have the correct amount of 0`s in days/months). However if you insert this
INSERT INTO #temptable
VALUES ( '14/03/012' )
SELECT CONVERT(DATETIME, RIGHT('00' + CAST(DAY(DateString) AS VARCHAR(2)), 2)
+ '/' + RIGHT('00' + CAST(MONTH(DateString) AS VARCHAR(2)), 2) + '/'
+ CAST(YEAR(DateString) AS VARCHAR(4)), 103)
FROM #temptable
WHERE ISDATE(DateString) = 1
It then fails on getting the year out of the date. Now i understand that isdate doesnt always work well with conversion - hence in 2012 MS are doing the try_convert and try_parse which will be brill.
However i cant wait for 2012 (or for us to upgrade once its released).
I would rather not have to move away from isdate as, as you can see the dates are a little random.
Any ideas how i can solve this problem?
TIA
Dan

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

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

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

Well, on the rare occasions that I've been forced to do something like this I would generally use a CLR regex.

If you need a pure SQL solution, would something like this work?

SELECT DateString =

CONVERT(DATETIME,CASE WHEN ISDATE(DateString) = 1 THEN DateString ELSE NULL END)

FROM (SELECT DateString =

CASE WHEN DateType = 1 THEN SUBSTRING(DateString, 7, 4) + SUBSTRING(DateString, 4, 2) + SUBSTRING(DateString, 1, 2)

WHEN DateType = 2 THEN SUBSTRING(DateString, 6, 4) + '0' + SUBSTRING(DateString, 4, 1) + SUBSTRING(DateString, 1, 2)

WHEN DateType = 3 THEN SUBSTRING(DateString, 5, 4) + '0' + SUBSTRING(DateString, 3, 1) + '0' + SUBSTRING(DateString, 1, 1)

WHEN DateType = 4 THEN SUBSTRING(DateString, 6, 4) + SUBSTRING(DateString, 3, 2) + '0' + SUBSTRING(DateString, 1, 1) END

FROM (SELECT DateString, DateType =

CASE WHEN DateString LIKE '[0-3][0-9]/[0-1][0-9]/[1-9][0-9][0-9][0-9]'

THEN 1

WHEN DateString LIKE '[0-3][0-9]/[0-9]/[1-9][0-9][0-9][0-9]'

THEN 2

WHEN DateString LIKE '[0-9]/[0-9]/[1-9][0-9][0-9][0-9]'

THEN 3

WHEN DateString LIKE '[0-9]/[0-1][0-9]/[1-9][0-9][0-9][0-9]' THEN 4 ELSE 0 END

FROM #temptable) a

) b

WHERE DateString IS NOT NULL;

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/

danielfountain SSCarpal Tunnel Points: 4229 More actions · Answer 2

Thanks all for your advice.

Taken on board and working on a solution.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

We can do this and keep it simple.

WITH

cteFixDates AS

(

SELECT FixedDate = REPLACE(REPLACE(DATESTRING,'/0','/'),'.0','.')

FROM #TempTable

)

SELECT CONVERT(CHAR(10),CAST(FixedDate AS DateTime),103)

FROM cteFixDates

WHERE ISDATE(FixedDate) = 1

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)