varchar to date derived tables

• Peter de W

  SSC Enthusiast

  Points: 146

  More actions

  April 6, 2009 at 9:14 pm

  #202535

  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
  

• Jeff Moden

  SSC Guru

  Points: 1004641

  More actions

  April 6, 2009 at 11:06 pm

  #973095

  Op accidently posted in wrong forum. This thread answered in the following post...

  http://www.sqlservercentral.com/Forums/Topic691647-169-1.aspx

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