[RESOLVED] Pulling dates out of strings

Hi Guys

I've got a table with a varchar column that has been used to hold dates. Not great, I know, but that's the siutation as it is. Worse, it often contains other 'flavour' text like "entered on" or similar. I've now got a requirement to report out the dates from this column. And worse still, the daytes are in different formats, ie with slashes(01/01/12), dots(01.01.12), straight numeric (010112), with and without centuries... you name it, it's in there. About the only the oonly think I can reasonably rely on is that they'll follow the DMY ordering.

I'm trying to think if there's a way I can parse those dates out. I can probably write an algorithm using replace and charindex etc but the number of permutations that could have been entered is huge. Any suggestions or am I just stuffed?

What I would probably do is create an extra column in that table (or in another table), and run a series of Update statements to fill it - each based on a different expected format (each using Where NewField Is Null , plus whatever conditions are apt for the format).

That should allow you to get the correct data, and at each stage you can see what format(s) the remaining data has.


Alternatively you could create a Function which somehow reads just the numeric characters (and apt delimiters), and then parses them as apt.

may be this will help you..

Code:

---


CREATE FUNCTION GetDatePart
(
@DateString nvarchar(255)
)
RETURNS nvarchar(255)
AS
BEGIN
declare @DatePart nvarchar(255)

declare @v1 int, @v2 int, @v3 char

select @v1=1, @v2=len(@DateString)

SET @DatePart = ''

while @v1<=@v2
begin
  set @v3=upper(substring(@DateString,@v1,1))
    IF  isnumeric(@v3)=1 or @v3 ='/'
        SET @DatePart = @DatePart + @v3
  set @v1=@v1+1
end
-- To convert 010112 into 01/01/12
if len(@DatePart)=6
set @DatePart = substring(@DatePart,1,2) + '/' + substring(@DatePart,3,2) + '/' +substring(@DatePart,5,2)

return(@DatePart)
END

---

and simply call it

Code:

---

select convert(datetime,dbo.GetDatePart(MyColumn)) FROM MyTable

---

Thanks for the suggestions guys.

I think a UDF is probably our way forward but the problem here is that the unit I'm working for can't actually make ammendments in the DB so we'd have to create a request to get the UDF developed.

In the meantime we've done this:-

Code:

---

select LotFeatureValue,
                case PATINDEX('%[a-Z,@]%', replace(replace(replace(replace(REPLACE(LotFeatureValue, ' ', '@'), '/', ''), '-', ''), '.', ''), '(' ,''))
                when 7
              then substring(replace(replace(replace(replace(REPLACE(LotFeatureValue, ' ', '@'), '/', ''), '-', ''), '.', ''), '(' ,''),1,2)
                      +'/'+
                      substring(replace(replace(replace(replace(REPLACE(LotFeatureValue, ' ', '@'), '/', ''), '-', ''), '.', ''), '(' ,''),3,2)
                      +'/20'+
                      substring(replace(replace(replace(replace(REPLACE(LotFeatureValue, ' ', '@'), '/', ''), '-', ''), '.', ''), '(' ,''),5,2)
            when 9
                  then substring(replace(replace(replace(replace(REPLACE(LotFeatureValue, ' ', '@'), '/', ''), '-', ''), '.', ''), '(' ,''),1,2)
                      +'/'+
                      substring(replace(replace(replace(replace(REPLACE(LotFeatureValue, ' ', '@'), '/', ''), '-', ''), '.', ''), '(' ,''),3,2)
              +'/'+
              substring(replace(replace(replace(replace(REPLACE(LotFeatureValue, ' ', '@'), '/', ''), '-', ''), '.', ''), '(' ,''),5,4)
                else
              case when ISNUMERIC(replace(replace(replace(replace(REPLACE(LotFeatureValue, ' ', '@'), '/', ''), '-', ''), '.', ''), '(' ,'')) = 1
              and LEN(replace(replace(replace(replace(REPLACE(LotFeatureValue, ' ', '@'), '/', ''), '-', ''), '.', ''), '(' ,'')) = 8
                                        then substring(replace(replace(replace(replace(REPLACE(LotFeatureValue, ' ', '@'), '/', ''), '-', ''), '.', ''), '(' ,''),1,2)
                                                +'/'+
                                                substring(replace(replace(replace(replace(REPLACE(LotFeatureValue, ' ', '@'), '/', ''), '-', ''), '.', ''), '(' ,''),3,2)
                                                +'/'+
                                                substring(replace(replace(replace(replace(REPLACE(LotFeatureValue, ' ', '@'), '/', ''), '-', ''), '.', ''), '(' ,''),5,4)
              when ISNUMERIC(replace(replace(replace(replace(REPLACE(LotFeatureValue, ' ', '@'), '/', ''), '-', ''), '.', ''), '(' ,'')) = 1
              and LEN(replace(replace(replace(replace(REPLACE(LotFeatureValue, ' ', '@'), '/', ''), '-', ''), '.', ''), '(' ,'')) = 6
                            then substring(replace(replace(replace(replace(REPLACE(LotFeatureValue, ' ', '@'), '/', ''), '-', ''), '.', ''), '(' ,''),1,2)
                                                +'/'+
                                                substring(replace(replace(replace(replace(REPLACE(LotFeatureValue, ' ', '@'), '/', ''), '-', ''), '.', ''), '(' ,''),3,2)
                                            +'/20'+
                                            substring(replace(replace(replace(replace(REPLACE(LotFeatureValue, ' ', '@'), '/', ''), '-', ''), '.', ''), '(' ,''),5,2)
                      else '01/01/1900'
      end
end
From VariableLotFeatureValues

---

That's giving us the result we want given the data we're got already but it's going to fall over as soon as someone enters something else we don't expect:rolleyes: Sadly I don't think it's an option to get proper date handling enforced on teh field because it's coming from a third party system. Aargh.