Converting Varchar to Date time

[wujtehacjusz]

Hi,

I am having problems converting some dates on my database.

I have a table which contains submissions from our partners in XML. One of the fields submitted is date of birth for the customer. Regardless to the fact that all providers are told how this should be submitted some still can't do this correctly. Hence I end up with a lot of varchars which are dates, but in all sorts of formats.

Is there and easy way to handle this conversion?

Thanks.

[rmiao]

Can't think about any easier way than enforce format in frontend.

[wujtehacjusz]

We alredy do, but experience proven that a lot of people are not capable of doing what they are told... :/

Thanks for reply.

[skhanal]

You can write a function to convert different formats to one common format.

[wujtehacjusz]

Tried that. The issue is my inability of determining the date format before I run the function/query. This would mean that I will need a function which will cover all possibilities.

[SDas]

wujtehacjusz, From what you describe (the date can be in ANY format), I don't think there is any way to determine the actual date.

For example, if some one enters 03 06 09, it could be:
Mar 6 2009
Mar 9 2006
Jun 3 2009
Jun 9 2003
Sep 3 2006
Sep 6 2003

Since you have no idea what order the Year, Month and Day are in, all of these values could be valid (and you would have a 5 in 6 chance of being wrong).

About the only way I can think of to even attempt this is to take all the values from one source and put them into 3 separate columns. Then do some queries and see if:

A column could be a Year
If it is a 4 digit number.
All the columns have a 2 digit value that is 1 (or very few) number(s) that match or are around the current year.
(sorry, I just re-read the original post and the date is birth date. This would make it very difficult to determine if a 2 digit value is a Year).

A column is a day if contains values greater than 12 (assuming it is not a year).

A column could be a month if it only contains values between 1 and 12.

NOTE: there may be other factors to check that could help determine the Year, Month, and Day positions.

Alternately, you may be able to generate a list of the various sources and the format that each one uses. If each source is consistent in the format they supply, you might be able to set up special processes based on the supplier to parse out the correct date.

[MAK]

YOu could write a function or use case function to update the data.

[wujtehacjusz]

Problem I have is that unsuccesful conversion will throw an excepton which will prevent the execution. Sadly I can't use try catch in functions...

Thanks for reply.

[Island1]

If date can be in "any format," you've got a real challenge... Can you at least assume that the format is consistent for all dates from a given provider? If so, and if you can derive a rule for each provider's format, you could try to apply the rule to the respective providers submissions - perhaps you could group by provider / create provider views / pump into a staging table, etc., and then try to get all dates to the same format before doing further conversions.

You could even do something like this with expressions in Reporting Services, but you really need to get the existing, underlying data consistent - and then apply (and enforce) new rules going forward (mechanically - not just through requests, etc.)

Just some ideas ...

Bill