June 18, 2008 at 5:30 am
Hi there,
Is it somehow possible to insert dates in a datetime column which are earlier than 1753 A.D. or even B.C. dates?
insert into _test values('1752-12-31')
insert into _test values('1207-01-01')
insert into _test values('0207-01-01')
All those statements raise exceptions: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Using strings is not acceptable. I have to do sophisticated operations on these datetimes. Is there any chance?
Greetz: Dankwart
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]
June 18, 2008 at 5:40 am
Sorry, but as far as I know, it isn't possible... we only needed such out-of-range dates once, and that was only for storage (no calculations performed on the column), so we decided to use VARCHAR.
Maybe the only solution would be to create a table with list of all dates (day-by-day) before the critical date - as far back as could be needed - and use a second column as a counter.
June 18, 2008 at 6:20 am
Thanks Vladan,
the idea is good, but unfortunately not applicable in my case. I have thousands of insert statements where only a minimal subset has data of that type, ranging from 0000 to 1753. In most cases it is an input mistake in the front end application anyway. Unfortunately I'm not in charge of that front end so I cannot intercept the mistaken input and prefer to insert into the db what the front end offers (even though if it is a date before 1753).
If this is not possible as you say - can I intercept the statement? I don't want this exception to raise - I'd rather have the mistaken date changed to 1900-01-01 or null (or whatever) silently. How can I do that? Is there something similar to "set ansi_warnings off" - e. g. like "set date_warnings off"?
Cheers, Dankwart
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]
June 18, 2008 at 6:46 am
No, because it's not a warning. It's the same as trying to insert 'abc' into an integer column. It's a data conversion error.
I would suggest that you load into a temp table with the column declared as varchar, then copy to the real table only those rows where IsDate(<Column Name> ) = 1
Maybe a trigger, though I don't usually like such things.
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
June 18, 2008 at 6:48 am
CLR Integration Data Types ?
June 18, 2008 at 6:52 am
So from what I understand you dont have control over the insert statements... Have you thought of writing a trigger on that table to intercept the SQL statement?
June 18, 2008 at 6:52 am
Thats a first step. But I don't want the whole row to be eliminated, only the datetime value reset to be null or something else. Is there another solution than using a stored procedure?
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]
June 18, 2008 at 6:55 am
Mark Beringer (6/18/2008)
So from what I understand you dont have control over the insert statements... Have you thought of writing a trigger on that table to intercept the SQL statement?
Exactly. And a trigger sounds reasonable. Thanks!
Dankwart
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]
June 18, 2008 at 6:58 am
dankwart menor (6/18/2008)
Thats a first step. But I don't want the whole row to be eliminated, only the datetime value reset to be null or something else. Is there another solution than using a stored procedure?
Thinking abot it a bit more, an instead of trigger on the table is exactly what you need.
It intercepts the insert statement and lets you rewrite the statement before hitting the table and firing data type errors.
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
June 18, 2008 at 7:04 am
This trigger is executed for every insert statement, isn't it? I'm a little worried about performance issues: Can't I just fire it if the error occurs? Like a try/except mechanism?
Best Regards
Dankwart
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]
June 18, 2008 at 7:11 am
Yes, it is. Write it properly and there won't be much of an impact.
Since the instead of fires before the data is inserted, you can't fire it only when there's an error. The trigger fires before SQL knows if there's an error
You also can't do the check and fix in an After trigger, since an after trigger won't fire if there's a data conversion error.
All you really need is something like this for the trigger. (pseudo code and made up table names)
CREATE TRIGGER trg_FixDumbDates ON someTable
INSTEAD OF INSERT
AS
Insert into SomeTable (<Column list> )
Select Col1, Col2, Col3,
CASE ISDATE(BrokenDate) WHEN 1 then BrokenDate ELSE '1900/01/01' END
FROM inserted
GO
What you're doing is intercepting all insert statements and rewriting them.
Edit: Fixed stupid error in code.
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
June 18, 2008 at 7:29 am
Yip, if you create an INSTEAD OF INSERT TRIGGER on your table it will pick up each instance where an insert statements is being executed on your table. I am not to sure about the performance, might slow down things abit, try testing it and let us know what the results are
🙂
June 18, 2008 at 8:01 am
This is how I would probably go about such problem...
You will need to have 2 columns in the table for this date - one datetime and one varchar. Then make sure all the inserts target the varchar column, and use a trigger to write converted value only if the conversion is possible.
CREATE TABLE testdates(tid int identity, somecol varchar(10), chardate varchar(10), mydate datetime)
GO
CREATE TRIGGER trg_write_date ON testdates
AFTER INSERT
AS
UPDATE t
SET mydate = i.chardate
FROM inserted i
JOIN testdates t ON t.tid=i.tid
WHERE ISDATE(i.chardate)=1
GO
/*test*/
insert into testdates(somecol, chardate) VALUES ('test1','20080701')
insert into testdates(somecol, chardate) VALUES ('test2','10080701')
I tried to come up with some INSTEAD OF trigger, but whatever I did, it looked to me more complicated than this. And more, you can use AFTER trigger for both INSERT and UPDATE, if that is necessary... just need to add another piece of code to treat setting to NULL.
ALTER TRIGGER trg_write_date ON testdates
AFTER INSERT, UPDATE
AS
UPDATE t
SET mydate = i.chardate
FROM inserted i
JOIN testdates t ON t.tid=i.tid
WHERE ISDATE(i.chardate)=1
UPDATE t
SET mydate = NULL
FROM inserted i
JOIN testdates t ON t.tid=i.tid
WHERE ISDATE(i.chardate)=0
Triggers will slow down the processing, but it shouldn't be critical. Test it... and you'll see. There is no way to say beforehand.
June 18, 2008 at 8:05 am
Gail,
did you test your INSTEAD OF trigger? I tried to do it approximately the same way, but it didn't intercept the error. I was still getting the conversion error message... but maybe I had some stupid mistake in the script.
June 18, 2008 at 8:09 am
No. I didn't.
Will give it a shot when I get home, If I have a chance.
SQL shouldn't be doing the data type test before the actual insert. Instead ofs are supposed to fire before things are checked. It's kinda the point of them Hmmmm
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 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply