One solution I used was a look up tale with one DATE column and a
bunch of string columns to hold various "local dialect" dates. Scrub
up the raw data a little bit and do string matches.

It was easy to fill in with a spreadsheet that had options. The nice
part was that an ambiguous date (mm-dd versus dd-mm) was easy to
find. 20 years was more than enough for my purposes.

That's a great idea, and I actually already tried it. It is fast, and
makes intuitive sense, which is always a plus to me.

The problem is that I want to have the lookup fire as part of a
computed persisted column.

I don't like persisting derved data, but it makes sense in this case,
because it's a write-once read-many situation. Given that it is going
to be persisted, the choice is between a persisted computed column and
a trigger, and I like the computed column much better.

Since the computed column has to be some kind of function, I just
wrote a simple function (generally not a fan of scalar functions, but
in this case, it seemed a good solution) that looks up the date and
returns it. Problem, this function is non-deterministic, so I can't
use it in a PERSISTED column.

I'm amazed that MSFT doesn't allow one to supply the format string for
ISDATE, and thus make it deterministic.

Thanks,

Bill

On Nov 3, 5:29*am, --CELKO-- <jcelko... (AT) earthlink (DOT) net> wrote:
> One solution I used was a look up tale with one DATE ...

If they would come up to ANSI/ISO Standards then the ONLY format would
be yyyy-mm-dd. The new DATE data type might do that

On Tue, 3 Nov 2009 16:22:22 -0800 (PST), bill wrote:

Hi Bill,

Have you considered using a materialized view, as a third alternative?

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis