June 18, 2008 at 8:17 am
Suresh B. (6/18/2008)
CLR Integration Data Types ?
Yeaup... and what would you have it do? Fact is, you still can't store out of range dates in the database.
If the front end can't be changed to do things correctly, store the dates as VARCHAR and maybe add a calculated column...
=CASE WHEN ISDATE(datecolumn)=1 THEN datecolumn ELSE '19000101' END
Do keep in mind that '2008' and the like will return an ISDATE = 1 because it will convert to 20080101 as a datetime.
The calculated column can become quite complicated depending on all the checks you want to make. Perhaps a trigger to check the dates and replace them would be better.
The absolutely best thing to do would be to get the folks that wrote the GUI code to fix their bloody code 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2008 at 8:18 am
Well, that's true, but on the other hand, where are you getting the "BrokenDate" from (during the INSERT ... FROM inserted inside the trigger)? That is, what datatype is "BrokenDate"?
If you just take the column that already is in the database (DATETIME), then the conversion error must arise - how would you get the wrong character data into table INSERTED? The data types in Inserted are the same as in the actual table.
That means, even with INSTEAD OF trigger you must have 2 columns (one datetime and one for character data), and redirect all the inserts to the column with character data. Or did I miss something? It is not apparent from your simplified pseudocode... but I'n afraid it won't work unless 2 columns are used.
June 18, 2008 at 8:23 am
Jeff,
calculated column was my first idea, but when I tested it, I found out that it can't be made persisted because of ISDATE function (and also probably can't be indexed for the same reason, if I understood BOL correctly)... so I started to explore triggers.
Fixing the problem at its source is always best, I absolutely agree that the front-end application should check validity.
June 18, 2008 at 8:36 am
Front end or insert proc should test for validity.
This made me think about how I would go about creating a family tree or history database with years outside the range for datetime. I'm gonna have to mess around with a user-defined data type now. Haven't done that before, might be interesting...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 18, 2008 at 9:04 am
Thanks a lot - all of you.
Apparently it would always be best to validate the user input at the user level. But I cannot do anything about that.
Now I use a varchar column instead of datetime. Then I check if it is a valid date. If not I force it to be one. After that I alter the column to be of type datetime.
Thats pretty easy and also it's quite fast (with 150000 rows and 3 of 12 columns to be converted)
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]
June 18, 2008 at 9:21 am
GSquared (6/18/2008)
Front end or insert proc should test for validity.This made me think about how I would go about creating a family tree or history database with years outside the range for datetime. I'm gonna have to mess around with a user-defined data type now. Haven't done that before, might be interesting...
Turns out, to get the behavior I want, I'm better off with a calendar table with a primary key that translates to the date. User-defined data type didn't do what I wanted with any performance at all, but an extended calendar table works quite well.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 18, 2008 at 2:55 pm
Oracle or another DB for what? An extended range calendar? Why would I bother? Took me about 10 minutes to throw something together in SQL that works beautifully and includes dates from 2000 BC to 3000 AD, with correct numbers of days in Feb, etc., all the way back. Can extend it back as far as I want to, or ahead, very easily.
Could easily add columns to it for Julian, pre-Julian, Hebrew, etc., calendars and conversion back and forth between them. Would just have to baseline it, and I'd have them.
Can do the equivalent of datediff with it quite easily, and dateadd. Not native SQL functions, but performant enough, and work for any dates at all, so long as they are in the table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 19, 2008 at 4:13 am
Vladan (6/18/2008)
The data types in Inserted are the same as in the actual table.
Good point. Would need 2 columns then. 1 a varchar that the insert puts the 'date' into, and a real datetime that the instead of populates.
There's no 'nice' solution to this
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply