varchar to date distinct select

Question

Post reply

varchar to date distinct select

Peter de W

SSC Enthusiast

Points: 146
More actions
April 6, 2009 at 9:56 pm

#203220

Sorry I originally put this in SQL 2005 but it is a 2000 DB
I have been assigned the task of searching a database for birthdates of a particular month.
The problem is the dates were stored in the database in a varchar(255) field where at point of data entry there was no date validation or formatting.
Hence the entries are wide and varied. Some examples
* 290704
* 29 July 2004
* 29/07/2004
* 29th July 2004
* July 2004
* 29.07.2004
* 29/Jul/2004
* 2004
* Wednesday 29th July 2004
* July
* Null
* dd/MM/yyyy
* etc. you name it its there
I have run a query with an isDate( birthDate1)=1 but this obviously won't help when searching for a particular month as 2009 is returned as a valid date. So when i add another filter where isDate( birthDate1)=1 and DatePart(month, birthDate1)=@month then i get all these errors for "converting to datetime from character string".
Apart from telling the client to go away can anyone help me?
There is four birthday fields and this is what I have so far. I am open to any suggestions.(coding that is)
declare @month int
declare @year int
set @month=7
set @year=2007
set dateformat dmy
select distinct c.[id], firstname, lastname, b1.childname1,b1.childdate1,b2.childname2, b2.childdate2, b3.childname3, b3.childdate3,
b4.childname4, b4.childdate4
from customers c
left join (
select [id], childname1, childdate1 from customers
where (DatePart(month, CASE ISDATE([childdate1]) WHEN 1
THEN CAST([childdate1] AS DATETIME)
ELSE CAST(NULL AS DATETIME) END )=@month and ISDATE([childdate1])=1)) b1 on c.id=b1.id
left join
(select [id],childname2, childdate2 from customers
where (DatePart(month, CASE ISDATE([childdate2]) WHEN 1
THEN CAST([childdate2] AS DATETIME)
ELSE CAST(NULL AS DATETIME) END )=@month and ISDATE([childdate2])=1)) b2 on c.id=b2.id
left join
(select [id], childname3, childdate3 from customers
where (DatePart(month, CASE ISDATE([childdate3]) WHEN 1
THEN CAST([childdate3] AS DATETIME)
ELSE CAST(NULL AS DATETIME) END )=@month and ISDATE([childdate3])=1)) b3 on c.id=b3.id
left join
(select [id], childname4, childdate4 from customers
where (DatePart(month, CASE ISDATE([childdate4]) WHEN 1
THEN CAST([childdate4] AS DATETIME)
ELSE CAST(NULL AS DATETIME) END )=@month and ISDATE([childdate4])=1)) b4 on c.id=b4.id
Thanks
Pete
Regards,
PdW

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

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

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 1

This will likely be slow one way or the other, so you may want to turn it into a function. This seems to catch most

of the aberrations you've listed and lists the month of the date.

[font="Courier New"]--===== Create a test table. This is NOT part of the solution.

IF OBJECT_ID('TempDB..#YourTable') IS NOT NULL

DROP TABLE #YourTable

CREATE TABLE #YourTable

(

SomeDate VARCHAR(256)

)

--===== Populate the test table with data. This is NOT part of the solution.

INSERT INTO #YourTable

(SomeDate)

SELECT '290704' UNION ALL

SELECT '29 July 2004' UNION ALL

SELECT '29/07/2004' UNION ALL

SELECT '29th July 2004' UNION ALL

SELECT 'July 2004' UNION ALL

SELECT '29.07.2004' UNION ALL

SELECT '29/Jul/2004' UNION ALL

SELECT '2004' UNION ALL

SELECT 'Wednesday 29th July 2004' UNION ALL

SELECT 'July' UNION ALL

SELECT 'Null' UNION ALL

SELECT 'dd/MM/yyyy'

--===== Set the European date format.

-- You may not need to do this.

SET DATEFORMAT dmy

--===== Salvage as many dates as possible and return the month for those you can.

-- This is just one possible solution... better one would probably be RegEx.

SELECT SomeDate, Cleaned, CASE WHEN ISDATE(Cleaned)=1 THEN DATEPART(mm,Cleaned) END AS Month

FROM (

SELECT SomeDate, ISDATE(SomeDate) AS IsDate,

REPLACE(

CASE

WHEN SomeDate NOT LIKE '%[0-9]%'

OR LEN(SomeDate) < 6

THEN NULL

WHEN SomeDate LIKE '%[0-9][snrt][tdh][ ]%' --1st,2nd,3rd,4th,21st,etc

THEN STUFF(SomeDate,PATINDEX('%[0-9][snrt][tdh][ ]%',SomeDate)+1,2,'')

ELSE SomeDate

END

,'Monday','')

,'Tuesday','')

,'Wednesday','')

,'Thursday','')

,'Friday','')

,'Saturday','')

,'Sunday','') AS Cleaned

FROM #YourTable

)d

[/font]

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

--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)

Peter de W SSC Enthusiast Points: 146 More actions · Answer 2

thanks jeff

I seem to be able to clean the most of them with the current query, i just end up with some rows where none of the data matched the month searched for. either the fields are NULL or an date from a different month.

I trying to clean out the rows with null values for all the childdate[1-4] fields.

Do i use union or joins?

Pete

Regards,

PdW

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

Apologies for the large delay in responding...

At this point, I'm not sure... guess I'd have to play with it. I'd probably clean all the data so that anything that wasn't a valid date was changed to NULL and then I'd hammer on things from there. Since you have a couple of child date columns, I'd imagine that a couple of joins (almost like your orginal code) would be necessary to do what you want in a setbased fashion.

Again... sorry for the delay.