Why?
-
I have the following code;
ISNULL(CI.InsuranceStartDate, '00-00-00') AS 'InsuranceStartDate'
But I get the following error;
Server: Msg 242, Level 16, State 3, Procedure USP_GetCreditorInsurance, Line 8
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Is there a way to achieve what I'm trying to do? Basically just want to return '00-00-00' instead of NULL that the LEFT OUTER JOIN will do if there is no record in the CompanyInsurance Table.
Keep in mind that the InsuranceStartDate Column has a DEFAULT VALUE of '0-0-0'. The rest of the stored procedure is below;
SELECT Cr.CRED_NAME AS 'Company', Cr.CRED_STATE AS 'State', ISNULL(I.InsurerName, '') AS 'Insurer', ISNULL(IT.InsuranceType, '') AS 'InsuranceType',
ISNULL(CI.PolicyNumber, '') AS 'PolicyNumber', ISNULL(CI.InsuranceStartDate, '00-00-00') AS 'InsuranceStartDate',
CI.InsuranceEndDate
FROM Company Co INNER JOIN CREDITORS Cr ON Co.CRED_ID = Cr.CRED_ID
LEFT OUTER JOIN CompanyInsurance CI ON Co.CRED_ID = CI.CRED_ID
LEFT OUTER JOIN Insurer I ON CI.InsurerID = I.InsurerID
LEFT OUTER JOIN InsuranceTypes IT ON CI.InsuranceTypeID = IT.InsuranceTypeID
WHERE Co.CRED_ID = @CRED_ID
-
>>ISNULL(CI.InsuranceStartDate, '00-00-00') AS 'InsuranceStartDate'
'00-00-00' is not a valid date. You have 2 choices:
1) Return the column as a varchar in the result set
ISNULL(Cast(CI.InsuranceStartDate As varchar), '00-00-00') AS 'InsuranceStartDate'
2) Choose a different, valid date to represent a null value
ISNULL(CI.InsuranceStartDate, '01 Jan 1900') AS 'InsuranceStartDate'
-
ISNULL(CI.InsuranceStartDate, '01 Jan 1900') AS 'InsuranceStartDate'
I would rather prefer a save format like
ISNULL(CI.InsuranceStartDate, '19000101') AS 'InsuranceStartDate'
But why does an insurance not always have a starting date anyway? Curious how any calculations like actuarial reserves, mortality tables or something similar will look like with such a starting date liek 19000101. I also work in the insurance business and would rather treat such cases as unknown. Not to say that I really don't understand how there be a an insurance without a starting date. How can you have a policy number when you don't have a date when that policy becomes valid? (Sorry, due to the language barrier, I can't express this better. Hope you get the meaning
)--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Quoting Trigger:
"Basically just want to return '00-00-00' instead of NULL that the LEFT OUTER JOIN will do if there is no record in the CompanyInsurance Table."
Seems like they don't have any insurances without starting date - it is just a matter of what they want to display as a starting date if certain company has no active insurances. IMHO this is just a presentation matter that should be solved on the client side; SQL should return NULL and then the client can format it as required... but maybe that's not all or I have overlooked something?
Vladan
-
Thanks for the solutions guys! I have performed PW's method of converting it to a varchar.
Let me explain a few things that you guys have asked!
1) Firstly, a Company may not have an Insurance! Hence the LEFT OUTER JOIN
2) I do not allow NULLS in my Databases! Hence the DEFAULT VALUE of '0-0-0' for a DateTime Column.
3) This code is in a stored procedure that is executed from a C#.Net front-end Application. The result set is returned into a DataGrid and I do not wish to show the end user the value of NULL in a cell! I could have tested for NULL from C#, but I try and do as much as possible on the back end.
Is there any benefit in allowing NULLS on the Column and then let the Application handle the representation of the word NULL? To a user, they don't want to see the word NULL!
-
>>To a user, they don't want to see the word NULL!
Of course not ! That's why it's called the 'presentation' layer.
The value is unknown/indeterminate, so you you 'present' it to the user as such. However, you don't let the presentation drive the optimal DB design for it. If a null-able smalldatetime accurately captures the data, don't try to shoehorn the data into some non-nullable form just to satisfy the presentation layer. Is a 'fake' date of some extreme future date/time any better than a NULL ? It's only 74 years till the next Y2K-like problem of Sql Server 4-byte dates hitting the year 2079 problem
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply