 |
|
| Computers Forum Index » Computer - Databases - MS SQL Server » Computed PERSISTED column for dates that tests for... |
|
Page 1 of 1 |
|
| Author |
Message |
| bill... |
 Posted: Sun Nov 01, 2009 9:39 am |
|
|
|
Guest
|
What I want to do:
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(
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(
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(,CONVERT(DATE,DATE_STRING_TX,112),112)) = 0 THEN
NULL ELSE CONVERT(date,DATE_STRING_TX,112) END PERSISTED |
|
|
| Back to top |
|
|
|
| Erland Sommarskog... |
| Posted: Sun Nov 01, 2009 6:26 pm |
|
|
|
Guest
|
|
| Back to top |
|
|
|
| --CELKO--... |
| Posted: Tue Nov 03, 2009 1:29 pm |
|
|
|
Guest
|
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. |
|
|
| Back to top |
|
|
|
| bill... |
| Posted: Wed Nov 04, 2009 12:22 am |
|
|
|
Guest
|
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 (no spam) earthlink.net> wrote:
> One solution I used was a look up tale with one DATE ... |
|
|
| Back to top |
|
|
|
| --CELKO--... |
| Posted: Wed Nov 04, 2009 12:59 am |
|
|
|
Guest
|
Quote: I'm amazed that MSFT doesn't allow one to supply the format string for ISDATE, and thus make it deterministic.
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 |
|
|
| Back to top |
|
|
|
| Hugo Kornelis... |
| Posted: Thu Nov 05, 2009 1:56 am |
|
|
|
Guest
|
On Tue, 3 Nov 2009 16:22:22 -0800 (PST), bill wrote:
Quote: 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 |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Mon Nov 30, 2009 9:45 pm
|
|