Default Field Value for DateTime & SmallDateTime

In SQL Server 2000 / Asp.Net I am trying to use default values for all fields; hoping to eliminate nulls.

For number and character fields, the default is pretty obvious, but is there any empty value for a date field? I think a null there might be better than putting in a bogus date, at least it can be tested for.

Are there any more developend ideas on this question?

Many thanks
Mike ThomasHi, Mike.
The choise depends directly on the problem U r solving. Sometimes GETDATE() helps... just analize Ur task and make a corresponding conclusion: what value is permitable as a default one in the definite case...

Alex.sql

Default Field Value

Is there a way to set default values for a numeric field? I have several
fields that sometimes are null. I want the nulls to show as zero. I tried
iif(value is null, 0.00 , value) but it gives an error on the â'nullâ' word. I
tried â'IsNullâ', â'IsNothingâ' and â'IsNumericâ' all with the same error.
Any ideas?Try
iif(value=Nothing, 0.00 , value)
"tachtenberg" <tachtenberg@.discussions.microsoft.com> escribió en el mensaje
news:9FF1A567-6AB0-402F-A542-8205EB6AA195@.microsoft.com...
> Is there a way to set default values for a numeric field? I have several
> fields that sometimes are null. I want the nulls to show as zero. I
> tried
> iif(value is null, 0.00 , value) but it gives an error on the "null" word.
> I
> tried "IsNull", "IsNothing" and "IsNumeric" all with the same error.
> Any ideas?
>

Decision Statement

Hello all,
I need to generate a result based on 2 fields:
1. RelationshipID &
2. MaritalStatusID
The above fields are not embedded in the application as a
mandatory check box so some of the values maybe empty. I
need to get a list of all the members who have a
RelationshipID = 2 and if it is NULL look in
MaritalStatusID = 1 and if that is NULL, display the
result anyways. I do not need to show the values from
these fields as they are a part of my parameters (filters)
in the query. I am not a programmer and wanted to know how
would I write this in a IF THEN statement in SQL?
select Distinct pip.MemberID
from tblMemberInsurancePlan pip
Inner Join tblMember m on m.MemberID = pip.MemberID
Left Join ctblInsurancePlan ip on ip.InsurancePlanID =
pip.InsurancePlanID
Left Join ctblFamilyRelationship fr on fr.RelationshipID =
pip.RelationToSubscriber
Left Join ctblMaritalStatus ms on ms.MaritalStatusID =
m.MaritalStatusID
-- Filtering for Current insurance only
where (pip.InsurancePlanState in ('C') and
pip.InsurancePlanOrdinal = 1)
-- Filtering for Dependents only
and pip.MemberID <> pip.InsurancePlanSubscriberID
-- Filtering for 'CHILD' relationship to the subscriber
(and fr.RelationshipID = 2 or
-- Filter for Relationship = 2 or MaritalStatusID = 1
and ''''
I greatly appreciate any help in architecting the code in
this regard.
Thansk again.
Joshitry this...
select Distinct pip.MemberID
from tblMemberInsurancePlan pip
Inner Join tblMember m on m.MemberID = pip.MemberID
Left Join ctblInsurancePlan ip on ip.InsurancePlanID =
pip.InsurancePlanID
Left Join ctblFamilyRelationship fr on fr.RelationshipID =
pip.RelationToSubscriber
Left Join ctblMaritalStatus ms on ms.MaritalStatusID =
m.MaritalStatusID
-- Filtering for Current insurance only
where (pip.InsurancePlanState in ('C') and
pip.InsurancePlanOrdinal = 1)
-- Filtering for Dependents only
and pip.MemberID <> pip.InsurancePlanSubscriberID
-- Filtering for 'CHILD' relationship to the subscriber&&&-- Filter for
Relationship =2 or MaritalStatusID = 1--
or fr.RelationshipID = 2 or maritalstatusid = 1
"J. Joshi" wrote:

> Hello all,
> I need to generate a result based on 2 fields:
> 1. RelationshipID &
> 2. MaritalStatusID
> The above fields are not embedded in the application as a
> mandatory check box so some of the values maybe empty. I
> need to get a list of all the members who have a
> RelationshipID = 2 and if it is NULL look in
> MaritalStatusID = 1 and if that is NULL, display the
> result anyways. I do not need to show the values from
> these fields as they are a part of my parameters (filters)
> in the query. I am not a programmer and wanted to know how
> would I write this in a IF THEN statement in SQL?
> select Distinct pip.MemberID
> from tblMemberInsurancePlan pip
> Inner Join tblMember m on m.MemberID = pip.MemberID
> Left Join ctblInsurancePlan ip on ip.InsurancePlanID =
> pip.InsurancePlanID
> Left Join ctblFamilyRelationship fr on fr.RelationshipID =
> pip.RelationToSubscriber
> Left Join ctblMaritalStatus ms on ms.MaritalStatusID =
> m.MaritalStatusID
> -- Filtering for Current insurance only
> where (pip.InsurancePlanState in ('C') and
> pip.InsurancePlanOrdinal = 1)
> -- Filtering for Dependents only
> and pip.MemberID <> pip.InsurancePlanSubscriberID
> -- Filtering for 'CHILD' relationship to the subscriber
> (and fr.RelationshipID = 2 or
> -- Filter for Relationship = 2 or MaritalStatusID = 1
> and ''''
>
> I greatly appreciate any help in architecting the code in
> this regard.
> Thansk again.
> Joshi
>|||On Tue, 8 Feb 2005 10:41:46 -0800, J. Joshi wrote:

>I need to generate a result based on 2 fields:
>1. RelationshipID &
>2. MaritalStatusID
>The above fields are not embedded in the application as a
>mandatory check box so some of the values maybe empty. I
>need to get a list of all the members who have a
>RelationshipID = 2 and if it is NULL look in
>MaritalStatusID = 1 and if that is NULL, display the
>result anyways. I do not need to show the values from
>these fields as they are a part of my parameters (filters)
>in the query. I am not a programmer and wanted to know how
>would I write this in a IF THEN statement in SQL?
Hi Joshi,
You can do the selection in the WHERE clause of the query:
WHERE (other requirements)
AND ( RelationshipID = 2
OR (RelationshipID IS NULL AND ISNULL(MaritalStatusID, 1) = 1))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Decimals in view

Hello,

I want a view to always present my numeric fields with 2 decimals. In my table I have the following values in field "amount" (numeric(18,2))

181.25
176.5
170

I want the view to show
181.25
176.50
170.00

I have tried Cast but that doesn't seem to do the job.

Help!

RolfNote that this is very poor practice to use server side code to format data. Formatting ought to be done by the client.

With that said, you can use the Str() function to format it as a string.

-PatP

Decimal Places

I have 2 fields X and Y, both are type decimal(38,20). I need to have as many decimal places as possible in my calculations for these numbers. This is the first time I've had to deal with this type of "precise" data so if you see something completely wrong just say so...

Anyway, I need to divide 2 numbers to get an answer...

I'm dividing: -118.84 by 867561.993

selectcast(net_expenses/fund_shares_outstanding asdecimal(35,20)) from xyz where blah blah

My query returns: -0.00013600000000000000

This same calculation with the same numbers in Excel returns -0.00013698156553522500

What do I need to do to get the more precise answer?

Precision, scale, of output cannot exceede precision of input.

-0.00013698156553522500 is gibberish past the third decimal place unless you are working with

-118.84000000000000000000

and

867561.99300000000000000000

DECLARE @.float1float

DECLARE @.float2float

SET @.float1=-118.84

SET @.float2= 867561.993

SELECT @.float1/@.float2