Restrict Implicit Conversion.

Question

Restrict Implicit Conversion.

Satheesh E.P.

SSCrazy

Points: 2509
More actions
April 9, 2014 at 10:59 am

#306225

Hi All,
Create Table Test01(
business_date date)
1. Insert into test01 (business_date) values ('2013may6')
2. Insert into test01 (business_date) values ('20140409')
In the above example both the statement will succeed. But as per our requirement, the first statement should fail on inserting (i.e) if a date value is on text then it should file. Since the insertion is happening from a third party tool, the settings should be set on the table. Please help.
---------------------------------------------------
Thanks,
Satheesh.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 1

i wouldn't do this; the date format is valid, and since it goes into a date typed field, how does that adversely affect you?

what if an application passed a date or datetime field as a parameter? an implicit conversion from datetime to date would occur, which does not have a string pattern at all?

i don't think you can even capture this in a table trigger, because it would already be cast to the datatype of date in the INSERTED/DELETED tables by the time it hit the trigger.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Satheesh E.P. (4/9/2014)
Hi All,
Create Table Test01(
business_date date)
1. Insert into test01 (business_date) values ('2013may6')
2. Insert into test01 (business_date) values ('20140409')
In the above example both the statement will succeed. But as per our requirement, the first statement should fail on inserting (i.e) if a date value is on text then it should file. Since the insertion is happening from a third party tool, the settings should be set on the table. Please help.

I am unfortunately unaware of any way to prevent implicit data conversions where such conversion is successful. Realize that both of your inserts above rely on implicit data conversions to work.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

The best way to prevent implicit conversions is to explicitly convert them or design the tables and code to match in datatype and size.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Only way to do this is to implement a trigger, I recommend an INSTEAD OF trigger and do your validation in the trigger.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

All good suggestions. My favorite, though, is to put the hammer on the 3rd party providing the data and get them to clean up their act.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Cody Konior SSCarpal Tunnel Points: 4827 More actions · Answer 6

Jeff Moden (4/9/2014)
All good suggestions. My favorite, though, is to put the hammer on the 3rd party providing the data and get them to clean up their act.

I've never seen a hammer big enough to get any 3rd party to provide proper data 😀 "It loads into Excel and that's the standard!" I'm really jealous.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

Satheesh E.P. (4/9/2014)
Hi All,
Create Table Test01(
business_date date)

I have to agree with Lowell, that if the column datatype is a date, then the insert will succeed and be correct and should not cause you a problem.

If, however, the column data type is not really "date", but is, in fact, string based, then you could use a check constraint or a calendar table / foreign key to enforce good data.

Probably the best thing - if you can - is to make the third party use a stored procedure though, not insert directly into your tables.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);