Extracting a date from a string

Question

Extracting a date from a string

Paula-196779

SSCrazy

Points: 2575
More actions
November 22, 2005 at 2:39 am

#102945

Hi all,
Please help. I need to write a query which picks up everything with a date greater than 09/11/2005, but the field which holds the date is a jumble of stuff. It holds a username which can be any length, followed by a space, followed by a date which can be in varying format, followed by another space and some more rubbish. Is there a way of getting everything after the first space and before the second space and doing a 'like' on it?
Actually I think that most of the dates that I am trying to retrieve are in dd/mm/yyyy format but to be on the safe side I think I should try to do a proper search on the full table, catering for all formats that are there.
Any ideas?
e.g. of formats:
supdh01 09/11/2005 10:15:00
paul 9/11/05 10:15:00
nagano 9th Nov 05 10:15:00
etc etc
Many thanks,
Paula.

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

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

You're in some troubles here due to the inconsistent formats of dates. There's no do-it-all formula for catching all kinds of possible dateformats in one go, and especially not to guarantee that they are all valid dates, or read as the intended dates.

eg if you have 09/11/2005 and somewhere else 11/09/2005 and they are both intended to show the same date - there's no way to deduce that programatically. The best thing you can do is to review them by eye and try to decide which date is 'true' for either - november 9th, or september 11th..

Another problem comes with dates such as '9th Nov 05' - you can't use the formula 'first space -> 2nd space' for that.

Perhaps the best way to start is to just strip away the first word to the first space, so that all dates are leftmost, and then take it from there. That's pretty easy to do, example follows.

declare @junktext varchar(255)

set @junktext = 'supdh01 09/11/2005 10:15:00'

select ltrim(substring(@junktext, charindex(' ', @junktext), 255))

---------------------

09/11/2005 10:15:00

(1 row(s) affected)

Another question is how many rows this is about? thousands, millions?

/Kenneth

Paula-196779 SSCrazy Points: 2575 More actions · Answer 2

Thanks very much. We're talking of hundreds of rows so it's not too bad. I've picked it out manually but still feel as though I've missed stuff. And I'm also thinking about if this happens again with larger tables (we have a number of idiotic table designs it seems!)

I'll keep searching and try to write a procedure for the future. At least the immediate pressure is off now I've picked this one out manually.

Thanks for your help.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

The best way to work it out is to create scalar UDF for searching date value within supplied string.

You can have iterations inside UDF, do multiple comparisons, but your select statement will be simple as this:

SELECT dbo.GetDateFromString(VarcharColumn)

_____________
Code for TallyGenerator

CJohnson-232084 Default port Points: 1492 More actions · Answer 4

You've probably already considred this, but it sounds as though your tables seriously need to be redesigned. A field such as this with some (but not much) structure usually is the result of poor or insufficent analysis and design. It will continue to cause problems for you.

Is it possible to split this field into at least three (UserID, Date, The rest Of It)? Then you could enforce data integrity in the date field.

Paula-196779 SSCrazy Points: 2575 More actions · Answer 5

Yes you're exactly right. I've recently arrived here as DBA and I am gradually discovering more and more poorly designed databases. Awareness seems better now, so it's only older system which have these things in them, but it does cause problems!

Thanks everyone.