Computed PERSISTED column for dates that tests for valid date: How To
bill
Make a PERSISTED computed column that converts a string to a date, and
tests the string for valid dates before attempting the conversion.
Does anyone know how to make ISDATE deterministic (BOL indicates it
could happen)
Background:
I can't always guarantee the column to be converted is a valid date.
I wish i could, but the source is out of my control.
I would prefer not to PERSIST derived data, but I need to in this
case.
I planned to use ISDATE to test for legal dates in the conversion, but
SQL tells me the ISDATE function is non-deterministic. I get an error
like this:
Msg 4936, Level 16, State 1, Line 1
Computed column 'REAL_DATE_DT' in table 'TEST_DATE_CALC_COLUMN' cannot
be persisted because the column is non-deterministic.
BOL gives this apparently non-sensical explanation of how to make
ISDATE deterministic:
"ISDATE is deterministic only if you use it with the CONVERT function,
when the CONVERT style parameter is specified, and when style is not
equal to 0, 100, 9, or 109."
Thanks,
Bill
Test Script:
create TABLE TEST_DATE_CALC_COLUMN
(
DATE_STRING_TX varchar(8)
PRIMARY KEY (DATE_STRING_TX)
)
INSERT INTO TEST_DATE_CALC_COLUMN VALUES ('20090228'); /* Insert a
Valid date */
ALTER TABLE TEST_DATE_CALC_COLUMN ADD REAL_DATE_DT as CONVERT
(date,DATE_STRING_TX,112) PERSISTED /* Couldn't use CAST, need to be
deterministic */
select * from TEST_DATE_CALC_COLUMN -- Prove that the calculated
Persisted Column Works
/***********************************/
DROP TABLE TEST_DATE_CALC_COLUMN /* drop the table to start over */
create TABLE TEST_DATE_CALC_COLUMN
(
DATE_STRING_TX varchar(8)
PRIMARY KEY (DATE_STRING_TX)
)
/*
Couldn't use CAST, need to be deterministic
This addition works, but doesn't test for illegal dates
in DATE_STRING_TX.
*/
ALTER TABLE TEST_DATE_CALC_COLUMN ADD REAL_DATE_DT as CONVERT
(date,DATE_STRING_TX,112) PERSISTED
/*
Drop the column in prepartion
for adding with test for illegal date
*/
ALTER TABLE TEST_DATE_CALC_COLUMN DROP COLUMN REAL_DATE_DT
/*
Try to test for illegal dates, but can't add the column
see following error.
*/
ALTER TABLE TEST_DATE_CALC_COLUMN ADD REAL_DATE_DT as CASE WHEN ISDATE
(CONVERT(varchar(8),CONVERT(DATE,DATE_STRING_TX,112),112)) = 0 THEN
NULL ELSE CONVERT(date,DATE_STRING_TX,112) END PERSISTED
Erland Sommarskog
> Does anyone know how to make ISDATE deterministic (BOL indicates it
> could happen)
I can't see how it could, as you cannot specify a format code to it, and
the interpretation of some strings is dependend on DATEFORMAT and LANGUAGE
settings.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
--CELKO--
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.
bill
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...@earthlink.net> wrote:
> One solution I used was a look up tale with one DATE ...
--CELKO--
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
Hugo Kornelis
>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.
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