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?
>

Default datetime values?

I'm confused. I have two variables:
@.StartDate and @.EndDate. I have to figure out how to
execute this report each day through email grabbing
yesterdays data. I tried just for a test to put in GetDate
() and GetDate()-1 and I got an error. I'm wondering how
do I set two datetime variables for yesterday 12:00 AM to
today 12:00 AM. Please help,
Regards,
BryanBmurtha,
Try using DateAdd function like
=DateAdd(DateInterval.Day, -1, Today())
Regards,
Cem
"bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
news:46c301c47352$60dcc490$a601280a@.phx.gbl...
> I'm confused. I have two variables:
> @.StartDate and @.EndDate. I have to figure out how to
> execute this report each day through email grabbing
> yesterdays data. I tried just for a test to put in GetDate
> () and GetDate()-1 and I got an error. I'm wondering how
> do I set two datetime variables for yesterday 12:00 AM to
> today 12:00 AM. Please help,
> Regards,
> Bryan|||Or try:
=Today.AddDays(-1)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
news:e4cADS1cEHA.3988@.tk2msftngp13.phx.gbl...
>
> Bmurtha,
> Try using DateAdd function like
> =DateAdd(DateInterval.Day, -1, Today())
> Regards,
> Cem
>
> "bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
> news:46c301c47352$60dcc490$a601280a@.phx.gbl...
> > I'm confused. I have two variables:
> > @.StartDate and @.EndDate. I have to figure out how to
> > execute this report each day through email grabbing
> > yesterdays data. I tried just for a test to put in GetDate
> > () and GetDate()-1 and I got an error. I'm wondering how
> > do I set two datetime variables for yesterday 12:00 AM to
> > today 12:00 AM. Please help,
> >
> > Regards,
> > Bryan
>

Default DATE and uniqueidentifier parameters?

I have several stored procedures and to facilitate getting the output of the stored procedures we have been adding default values for all of the input parameters. This works fine with the exception of DATE and uniqueidentifier parameters. I have defined stored procedures like:

ALTER PROCEDURE [dbo].[proc_GetOrderReasonByOrderGroupId]

@.OrderGroupId uniqueidentifier = NEWID

AS

and

ALTER PROCEDURE [dbo].[proc_shippedPackages]

@.DateFrom datetime = GETDATE,

@.DateTo datetime = GETDATE

but when I execute the following

SET FMTONLY ON

exec proc_shippedPackages

SET FMTONLY OFF

I get

Msg 241, Level 16, State 1, Procedure proc_shippedPackages, Line 0

Conversion failed when converting datetime from character string.

Any suggestions? The same error occurs with setting a uniqueidentifier. I want to create a default parameter that will more of less ensure that the output is empty.

Thank you.

Kevin

You could try the example below.

Beware, though, that if you do need to explicitly set the values of the @.DateFrom and @.DateTo parameters to NULL when calling the stored procedure [as opposed to simply not providing values for these optional parameters] then the parameters will be assigned a value of GETDATE() during execution, which may or may not be what you want. If this does turn out to be a problem then you could use an arbitrary [but unlikely to be used] date as the default value and then check for that value rather than NULL when assigning the value of @.Now.

Chris

Code Snippet

CREATE PROCEDURE [dbo].[proc_shippedPackages]

@.DateFrom DATETIME = NULL,

@.DateTo DATETIME = NULL

AS

--Ensure that both variables are set to

--equal values if defaults are required.

DECLARE @.Now DATETIME

SET @.Now = GETDATE()

IF @.DateFrom IS NULL

BEGIN

SET @.DateFrom = @.Now

END

IF @.DateTo IS NULL

BEGIN

SET @.DateTo = @.Now

END

SELECT @.DateFrom, @.DateTo

GO

|||

It is not bad idea to have NON-NULL values, suppose if you need to store / pass the NULL value from your code the below code wont break.

Code Snippet

Alter PROCEDURE [dbo].[proc_GetOrderReasonByOrderGroupId]

@.OrderGroupId uniqueidentifier = 0x0

AS

Select@.OrderGroupId = Case When @.OrderGroupId = 0x0 Then NewId() Else @.OrderGroupId End

go

Alter PROCEDURE [dbo].[proc_shippedPackages]

@.DateFrom datetime = '1900-01-01 00:00:00.000',

@.DateTo datetime = '1900-01-01 00:00:00.000'

as

SET @.DateFrom = Case When @.DateFrom = '1900-01-01 00:00:00.000' Then GetDate() Else @.DateFrom End

SET @.DateTo = Case When @.DateTo = '1900-01-01 00:00:00.000' Then GetDate() Else @.DateTo End

|||GETDATE and NEWID are functions and require () after them, unlike VB. Try:

@.OrderGroupId uniqueidentifier = NEWID()

@.DateFrom datetime = GETDATE(),

@.DateTo datetime = GETDATE()

|||TPhillips -> You are wrong; SP params only support the constant/NULL value as default value.|||Yes, you are correct. Your method, mentioned earlier, is the way to fix this problem.

However, the () are still needed to execute the functions.

|||

Tom Phillips wrote:

Yes, you are correct. Your method, mentioned earlier, is the way to fix this problem.

However, the () are still needed to execute the functions.

If you check the syntax with the Sql Management Studio it complains if you add the ().

|||

Kevin,

You cannot use the NEWID() and GETDATE() functions as default values in the parameter definition.

DEFAULT value assignments must be deterministic. Non-deterministic functions are not permitted in that context.

If your intent is to make the parameters optional, use '01/01/1900' (or NULL), then in the first lines of the sproc, check the values and if = '01/01/1900' (or NULL), then set the values = getdate().

Your original attempt failed because you are setting the default values to the string constants 'GETDATE' and 'NEWID.

NEWID() and GETDATE() both require parentheses as previously mentioned.

|||

Tom Phillips wrote:

Yes, you are correct. Your method, mentioned earlier, is the way to fix this problem.

However, the () are still needed to execute the functions.

When I include the () I get:

Msg 102, Level 15, State 1, Procedure proc_GetCaseNotesByOrderGroupID, Line 4

Incorrect syntax near '('.

ALTER PROCEDURE [dbo].[proc_GetCaseNotesByOrderGroupID]

@.OrderGroupID uniqueidentifier = NEWID()

AS

|||As mentioned, you have to set the default to a "static", you cannot use a function on a default value.

What your code was doing without the () is equivalent to:

@.OrderGroupID uniqueidentifier = 'NEWID'

I assume you did not want the @.orderGroupID to be a string NEWID. I think this is a bug or at least hold over from Sybase which allows unquoted strings to be invisibly converted to a string.

The best way to do what you want is:

ALTER PROCEDURE [dbo].[proc_GetCaseNotesByOrderGroupID]

@.OrderGroupID uniqueidentifier = NULL -- or some other non-occurring number

AS

IF @.OrderGroupID IS NULL
SET @.OrderGroupID = NEWID()

sql

Default Column Values

I want to be able to set the default value of a column to be the next number
available, ie. max(MyColumn) + 1 (?).
Ordinarily, you would use an identity field for this, but a) we already have
one (primary key) and b) this value will possibly change such that several
rows will have the same MyColumn value.
Any suggestions?
Chris
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]You could either a) let your application control the insertion of data
(which is probaby the best solution because it simplifies the
validation of data on the data level) OR b) write a INSERT trigger to
find the max value and if this column is not specified, then insert the
business rule you specified.
I try to avoid triggers when I can, because I think it places a burden
on your database performance, and I do a lot of DTS bulk inserts (which
don't fire triggers by default).
Stu|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1124361243.951718.201220@.g49g2000cwa.googlegroups.com...
> You could either a) let your application control the insertion of data
> (which is probaby the best solution because it simplifies the
> validation of data on the data level) OR b) write a INSERT trigger to
> find the max value and if this column is not specified, then insert the
> business rule you specified.
> I try to avoid triggers when I can, because I think it places a burden
> on your database performance, and I do a lot of DTS bulk inserts (which
> don't fire triggers by default).
>
So the formula can't be used in the columns Default Value property?
Why would a simple trigger like that burden the server any more than an
extra query to the Db to determine the appropriate value? I'm not
disagreeing with you, I'm just curious...
Server load is not such a big issue for me, but then again, that's not
really a reason to ignore it...
Chris|||You could write a function that returns the max + 1, and use it as a default
value, but this solution will have issues:
create function dbo.fn_nextkey() returns int
as
begin
return coalesce((select max(keycol) + 1 from t1), 1);
end
go
create table t1
(
keycol int not null primary key default dbo.fn_nextkey(),
datacol varchar(10) not null
);
go
insert into t1(datacol) values('a');
insert into t1(datacol) values('b');
insert into t1(datacol) values('c');
select * from t1;
keycol datacol
-- --
1 a
2 b
3 c
Multiple processes inserting at the same time will get the same value, and
you will get pk violation errors that you'd need to trap and handle.
A better option would be to create a table that maintains the last assigned
value:
create table seq(val int not null);
insert into seq values(0);
And increment the value every time you need a new key using a stored
procedure:
create proc usp_nextkey @.o as int output
as
update seq set @.o = val = val + 1;
go
When you need a new key, invoke the proc as follows:
declare @.i as int;
exec usp_nextkey @.i output;
insert into t1 values(@.i, 'd');
BG, SQL Server MVP
www.SolidQualityLearning.com
"CJM" wrote:

> "Stu" <stuart.ainsworth@.gmail.com> wrote in message
> news:1124361243.951718.201220@.g49g2000cwa.googlegroups.com...
>
> So the formula can't be used in the columns Default Value property?
> Why would a simple trigger like that burden the server any more than an
> extra query to the Db to determine the appropriate value? I'm not
> disagreeing with you, I'm just curious...
> Server load is not such a big issue for me, but then again, that's not
> really a reason to ignore it...
> Chris
>
>|||> (which is probaby the best solution because it simplifies the
> validation of data on the data level)
In general, unless you have a magical way of preventing users from accessing
the data *except* through your application, there is no good place to put
data validation *except* in the data layer. YMMV.
A|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uXs1FL$oFHA.1044@.tk2msftngp13.phx.gbl...
> In general, unless you have a magical way of preventing users from
> accessing the data *except* through your application, there is no good
> place to put data validation *except* in the data layer. YMMV.
>
Aaron,
I agree with you here... So out of interest, would you calculate the next
value within the same SP that inserts the row, or would you us a trigger?
(or another alternative?)
Chris|||I guess it's a matter of scale; we tend to insert a lot of data at one
time, and I try to minimize the queries to my database as much as
possible. In this particular case, the trigger would not be onerous,
but I've seen some really, really bad triggers written that can suck
the life out a server. I just tend to avoid them; not that they're
always bad, but in most of our applications we try to have the data be
as clean as possible before inserting it into the database. In other
words, we do all the lookups and data prep on the business logic layer,
not in the database.
Again, it's a matter of scale; we insert a lot of data at a very high
rate of speed; the simpler the INSERT process is, the better.
Stu|||Personally, I like Itzik's solution, I just kind of cringe a bit at the
syntax:
update table set @.variable = column = column + 1;
But that's just a minor pet peeve I guess.
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:OSaS2Z$oFHA.568@.TK2MSFTNGP10.phx.gbl...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uXs1FL$oFHA.1044@.tk2msftngp13.phx.gbl...
> Aaron,
> I agree with you here... So out of interest, would you calculate the next
> value within the same SP that inserts the row, or would you us a trigger?
> (or another alternative?)
> Chris
>|||no magic; we just lock our data servers down pretty tight, using
application roles, etc. You are correct in that someone could bypass
our application, but we do our best to limit that possibility.
As far as validation goes, I agree. I'm just saying that validation
should be as simple as possible on the database level (e.g., is the
value with constrained parameters? Does it exist in a relationship
with other values?), and that more complex permutations should be
assigned at the business tier level before it gets written to the
database.
Stu
PS: in my previous posts, I used the term application in a broad sense,
encompassing both presentation and business logic tiers. Just wanted
to clarify.

Decrement values in an aditional column

Hi I have a table like this:

CLIENT Value

a 12
b 11
c 8
d 10
e 4
I want to decrement this values in an aditional column

CLIENT Value ACUM
a 12 12
b 11 11-12 = -1
c 8 8-11 = -3
d 10 10-8 = 2
e 4 4 -10 = -6

Thks for your help

Rgds

Harry

Try a variation of the solution provided to you in your previous post, "Accumulate values in an aditional column".

Hint: 'Greater Than'

|||

There are endless variations depending on table structure, data and what you want to do with the data. In a real world situation how would you know what to subtract from what in your example?

Here is one other example:

CREATE TABLE dbo.Balance

(

ID int

,Entry int

,RunningTotal int

)

TRUNCATE TABLE dbo.Balance

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(1,500,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(2,-400,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(3,-300,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(4,-200,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(5,-100,NULL)

UPDATE dbo.Balance

SET RunningTotal = RT2.RunningTotal

FROM dbo.Balance RT1

INNER JOIN

(

SELECT Entry

,(SELECT -SUM(-Entry) FROM dbo.Balance WHERE ID <= rt.ID ) As RunningTotal

FROM dbo.Balance rt

) RT2

ON RT1.Entry = RT2.Entry

SELECT * FROM dbo.Balance

IDEntryRunningTotal

1500500

2-400100

3-300-200

4-200-400

5-100-500

|||

I try this , but the values only are growing

the table with this input values must be

ID Entry RunningTotal

1 500 500

2 -400 -400 - 500 = -900

3 -300 -300-(-900) = 600

4 -200 -200-(-300) = 100

5 -100 -100 - 100 = -200

How coud I do this?

|||

You example makes no sense.

For ID = 4 you are subtracting the original value of the preceding row but for the other rows you are subtracting the output of the prior calculation.

|||As I previously indicated, take the solution offered you to increment the values, and change the [LESS THAN] to a [GREATER THEN], and you may get what you seek.|||

Yes sorry I make a mistaked , what i want to know is that..It means how to subtracting the original value of the preceding row and I my example I do this with all rows in ID =2 the value mus tbe --> -400 - 500 = -900

ID = 3 --> -300 - (-400) = -300+400 = 100

ID = 4 --> -200-(-300)= -200+300 = 100

The table must be:

ID Entry RunningTotal

1 500 500

2 -400 -400 - 500 = -900

3 -300 -300-(-400) = 100

4 -200 -200-(-300) = 100

5 -100 -100 -(-200) = 100

Im really sorry for the confusion but Its important for me how to make it I try with "less than" "greather than" but it doenst work.

|||

So for each row you want to calculate a value that equals the Entry value for that row minus the Entry valaue for the preceding row.

UPDATE dbo.Balance

SET RunningTotal = b1.entry - b2.entry

FROM dbo.Balance b1

INNER JOIN

(

SELECT ID, entry

FROM dbo.Balance

)b2

ON b1.ID = b2.ID + 1

IDEntryRunningTotal

1500NULL

2-400-900

3-300100

4-200100

5-100100

Decrement values in an aditional column

Hi I have a table like this:

CLIENT Value

a 12
b 11
c 8
d 10
e 4
I want to decrement this values in an aditional column

CLIENT Value ACUM
a 12 12
b 11 11-12 = -1
c 8 8-11 = -3
d 10 10-8 = 2
e 4 4 -10 = -6

Thks for your help

Rgds

Harry

Try a variation of the solution provided to you in your previous post, "Accumulate values in an aditional column".

Hint: 'Greater Than'

|||

There are endless variations depending on table structure, data and what you want to do with the data. In a real world situation how would you know what to subtract from what in your example?

Here is one other example:

CREATE TABLE dbo.Balance

(

ID int

,Entry int

,RunningTotal int

)

TRUNCATE TABLE dbo.Balance

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(1,500,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(2,-400,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(3,-300,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(4,-200,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(5,-100,NULL)

UPDATE dbo.Balance

SET RunningTotal = RT2.RunningTotal

FROM dbo.Balance RT1

INNER JOIN

(

SELECT Entry

,(SELECT -SUM(-Entry) FROM dbo.Balance WHERE ID <= rt.ID ) As RunningTotal

FROM dbo.Balance rt

) RT2

ON RT1.Entry = RT2.Entry

SELECT * FROM dbo.Balance

IDEntryRunningTotal

1500500

2-400100

3-300-200

4-200-400

5-100-500

|||

I try this , but the values only are growing

the table with this input values must be

ID Entry RunningTotal

1 500 500

2 -400 -400 - 500 = -900

3 -300 -300-(-900) = 600

4 -200 -200-(-300) = 100

5 -100 -100 - 100 = -200

How coud I do this?

|||

You example makes no sense.

For ID = 4 you are subtracting the original value of the preceding row but for the other rows you are subtracting the output of the prior calculation.

|||As I previously indicated, take the solution offered you to increment the values, and change the [LESS THAN] to a [GREATER THEN], and you may get what you seek.|||

Yes sorry I make a mistaked , what i want to know is that..It means how to subtracting the original value of the preceding row and I my example I do this with all rows in ID =2 the value mus tbe --> -400 - 500 = -900

ID = 3 --> -300 - (-400) = -300+400 = 100

ID = 4 --> -200-(-300)= -200+300 = 100

The table must be:

ID Entry RunningTotal

1 500 500

2 -400 -400 - 500 = -900

3 -300 -300-(-400) = 100

4 -200 -200-(-300) = 100

5 -100 -100 -(-200) = 100

Im really sorry for the confusion but Its important for me how to make it I try with "less than" "greather than" but it doenst work.

|||

So for each row you want to calculate a value that equals the Entry value for that row minus the Entry valaue for the preceding row.

UPDATE dbo.Balance

SET RunningTotal = b1.entry - b2.entry

FROM dbo.Balance b1

INNER JOIN

(

SELECT ID, entry

FROM dbo.Balance

)b2

ON b1.ID = b2.ID + 1

IDEntryRunningTotal

1500NULL

2-400-900

3-300100

4-200100

5-100100

Decomposing an XML Document into a table of path and values

Suppose that you had an XML Document like:

<a>1<b>2<c>3</c>4</b><d>5</d>6</a>

What would be the best way to decompose it such that you end up with a table like:

XPath Value
/a 123456
/a/b 234
/a/b/c 3
/a/d 6

I eventually want to join this table with another table that has XPath values in a column.

Thanks,
Wells

Here is one solution:

declare @.x xml

set @.x ='<a>1<b>2<c>3</c>4</b><d>5</d>6</a>'

select n.query('

for $node in .

return

for $i in //*[some $j in ./descendant-or-self::* satisfies $j is $node]

return text{concat("/", local-name($i))}

'), n.value('.', 'nvarchar(100)')

from @.x.nodes('//*') t(n)

The result is:

/a 123456
/a/b 234
/a/b/c 3
/a/d 5

|||

Thanks Adrian,

This does the job nicely.

Wells

Decoded values in report

Hi,
I have a report generated from a dataset which has the state and county
codes. I have some other general purpose datasets which have the correspoding
county/state codes and descriptions. How do I link these to the report so
that I can see the descriptions instead of the code? I tried to drag the
description field onto the report, but it only pulls up the first value.
For example,
Folder (fldr) State (n_cd)
1234 2
4444 3
(Decode Dataset)
State Cd (n_cd) Description (n_desc)
2 FL
3 WA
When I drop n_desc onto the n_cd field of dataset 1, I get FL for all rows,
since it is picking up only the first row.
Please help.
Thanks,
ArshadMake sure it says =Fields!n_desc and not =First(Fields!n_desc)
regards,
Stas K.|||I did that and it gives the message: "....Report item expressions can only
refer to fields within the current data set scope, or if inside an aggregate,
the specified data set scope."
Thanks,
Arshad
"Sorcerdon" wrote:
> Make sure it says =Fields!n_desc and not =First(Fields!n_desc)
> regards,
> Stas K.
>|||Mr. Syed,
It is not possible to merge/link 2 datasets in a single report control
or lookup a value across datasets.
Ideally, you'd create a single dataset where the translation/decoding
of the state code to a description happens via a join between tables.
Andy Potter|||Thanks to all for your help. They need to make this more explicit in the
documentation!
"Potter" wrote:
> Mr. Syed,
> It is not possible to merge/link 2 datasets in a single report control
> or lookup a value across datasets.
> Ideally, you'd create a single dataset where the translation/decoding
> of the state code to a description happens via a join between tables.
> Andy Potter
>

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

Decimals Converting Back to Integers? Whats Going On?

I am trying use the decimal data type for a field in SQL Server. When I input the values below, they round off.

73.827 Rounds to 74

1925.1 Rounds to 1925

119.79 Rounds to 120

What am I missing? Access never gave me this issue. Do you see any reason this would happen? I am entering the values into the table directly!

What do you mean by "entering the values into the table directly"? And, where are you seeing them rounded?

|||

If I open the table in Enterprise Manager and view all my data. I can add data as well. You know, direct data entry in a table.

When I type 2.756, It rounds to the nearest integer. 2.756 will round to 3. The datatype for the column is decimal. Seems simple problem, but I can't seem to find the resolution.

|||

defyant_2004:

If I open the table in Enterprise Manager and view all my data. I can add data as well. You know, direct data entry in a table.

When I type 2.756, It rounds to the nearest integer. 2.756 will round to 3. The datatype for the column is decimal. Seems simple problem, but I can't seem to find the resolution.

What precision and scale do you have the decimal field set for?

|||

By default the precision will be set to 0 for decimal fields. You might need to adjust it.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

Decimal values get truncated when using SqlDataReader in C# .net

The values that are being fetched from the database are not being read as is , when there are decimal values with 10 or more digits after the point, the values are truncated (approximated) to 8 digits

The approximation is not consistent in all the cases
Some times depending on the numbers for example 0.434000001 is truncated to 0.434.

This is happening when i read the values from the sql database using the SqlDataReader.GetValue method into an ArrayList in C# .NETLooks like you r datatype is (the flaky) float.

What is it?

Did you try defining it as decimal?

Decimal values

Ho do I pass a real value containing decimals to my stored procedure,
my values get truncated after the decimal..
any help!!!!!!!!!!!!!!!!!!!Can you post a repro on this? How is the parameter defined in the stored pro
cedure, for instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<aroraamit81@.gmail.com> wrote in message
news:1138208844.360294.126400@.g47g2000cwa.googlegroups.com...
> Ho do I pass a real value containing decimals to my stored procedure,
> my values get truncated after the decimal..
> any help!!!!!!!!!!!!!!!!!!!
>

Decimal values

Ho do I pass a real value containing decimals to my stored procedure,
my values get truncated after the decimal..
any help!!!!!!!!!!!!!!!!!!!
Can you post a repro on this? How is the parameter defined in the stored procedure, for instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<aroraamit81@.gmail.com> wrote in message
news:1138208844.360294.126400@.g47g2000cwa.googlegr oups.com...
> Ho do I pass a real value containing decimals to my stored procedure,
> my values get truncated after the decimal..
> any help!!!!!!!!!!!!!!!!!!!
>

Decimal values

Ho do I pass a real value containing decimals to my stored procedure,
my values get truncated after the decimal..
any help!!!!!!!!!!!!!!!!!!!Can you post a repro on this? How is the parameter defined in the stored procedure, for instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<aroraamit81@.gmail.com> wrote in message
news:1138208844.360294.126400@.g47g2000cwa.googlegroups.com...
> Ho do I pass a real value containing decimals to my stored procedure,
> my values get truncated after the decimal..
> any help!!!!!!!!!!!!!!!!!!!
>

Decimal limited to 4 digits to right of decimal?

I need to store decimal values: decimal(20,15) in my SQL Server 2005 database.

I load data from flat file, convert it using Data Conversion Task to decimal(with scale: 15) and try to save it using OLE DB Destination.

It works fine for 4 digits after the decimal (like 1.1234), but always failes for more than 4 digits (1.12345).

Is the decimal limited to scale 4 ?

Thank you for your help!

Anna

Nope. Should work. What is the error?|||

I got:

Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80004005 Description: "Invalid character value for cast specification".

Error: There was an error with input column "MyDecimalColumn" (10464) on input "OLE DB Destination Input" (10388).
The column status returned was: "Conversion failed because the data value overflowed the specified type."

Anna

|||That would indicate the destination in SQL is two small. What is it defined as?

Decimal field is rounding up my numbers

Hi,

I have a decimal field in SQL Server 2000 which has a precision value of 3 and scale 1. I will be storing values ranging from 0.5 to 10.0 in there. However, in my asp.net web form, if I select the value 2.5 from the DropDownList, SQL Server stores it as 3.

Can anyone tell me why this is happening and give me some pointers on what I can do to fix it? Your help is much appreciated.

how are you passing the values from the asp.net page? whats the datatype? can you post the asp.net code? also if you manually insert the values from query analyzer does it insert the right values?

|||

Hi,

I entered 2.5 using QueryAnalyzer and SQL Server stored it as 2. So the issue is with the way I defined this Decimal field in SQL Server. The problem is I don't see where I'm making a mistake. If I set the precision to 3 and scale to 1, I should be able to have values ranging from 0.5 to 10.0 in there, should I?

|||your scale is right. try increasing your precision to 5. also check if you are working with the right column.|||Nope... I enter 3.5, it stores it as 4.0. And I'm doing this Query Analyzer so there's no question about where the data is going.|||

SamU wrote:

Nope... I enter 3.5, it stores it as 4.0. And I'm doing this Query Analyzer so there's no question about where the data is going.

I have added a testDecimal column to my Test table, and set it up as a decimal field with a precision of 3 and a scale of 1, and I am not seeing this odd behavior. It is storing the data exactly as I supply it (3.5, .5, 10.0, etc.) Please explain how exactly you are "doing this in Query Analyzer" as QA does not give you the facility to directly update the data; you need to execute a query to do this. I used queries like this:

UPDATE test SET testDecimal = 3.5 WHERE ID = 4
SELECT testDecimal FROM test WHERE testDecimal IS NOT NULL

For reference, when I first added the testDecimal column through Enterprise Manager, the default length was 9, the precision was 18, and the scale was 0. I changed the precision to 3 and the scale to 1, and the length automatically changed to 5.|||

We may be narrowing this down. I'm actually inserting new values into a table and I'm doing this through a stored procedure. The parameter that inserts the value is defined as decimal. I'm including the code down below. Do I need any additional values that further define the parameter's precision and scale in the stored procedure? Looks like it's the stored procedure that's rounding the number up, not the table. Can anyone see an issue w/ this stored procedure?

Here's the Stored Procedure code. The parameter that inserts value into this field is @.JobLength.

ALTER PROCEDURE dbo.spTalentReleaseNew
(
@.EmployeeID smallint,
@.JobName varchar(200),
@.JobDate smalldatetime,
@.DealID int,
@.JobLength decimal,
@.TalentAgencyName varchar(200) = null,
@.TalentID int,
@.TalentRate smallmoney,
@.LocationRate smallmoney,
@.MakeUpRate smallmoney,
@.FoodStylistRate smallmoney,
@.LastUpdateTimeStamp datetime
)
AS
/* ObjectID = 221; This stored procedure creates a new Talent Release. */
INSERT INTO tblTalentRelease
(EmployeeID, JobName, JobDate, DealID,JobLength, TalentAgencyName, TalentID, TalentRate, LocationRate, MakeUpRate, FoodStylistRate,
LastUpdateTimeStamp, LastUpdatedBy)
VALUES (@.EmployeeID, @.JobName, @.JobDate, @.DealID,@.JobLength, @.TalentAgencyName, @.TalentID, @.TalentRate, @.LocationRate, @.MakeUpRate,
@.FoodStylistRate, @.LastUpdateTimeStamp, @.EmployeeID)

|||That's the problem. If you just declare the @.JobLength as decimal, it is created with a scale equal to zero as the default (and precision of 18). Set it like this instead:

@.JobLength decimal(3, 1)

and you should be fine.

Don|||THanks Don.

Decimal division results do not round as expected

Say that the values of Sev3Met = 1 and the value of Sev3Total = 2
Using this
round((Sev3Met/(Sev3Total + 0.0)),2),
I would expect .50
However when I code in SQL 2005 Express manager against a SQL 2005
database, the results display as 0.5000000000
Using this
round(Sev3Met/cast(Sev3Total as decimal(3,0)),2)
I get 0.500000
is there some setting I am missing? How can I get 0.50 'Try something like:
CONVERT(DECIMAL(5.2),<expression you want to display with 2 decimals> )
Roy Harvey
Beacon Falls, CT
On 28 Jun 2006 14:30:31 -0700, wxbuff@.aol.com wrote:

>Say that the values of Sev3Met = 1 and the value of Sev3Total = 2
>Using this
>round((Sev3Met/(Sev3Total + 0.0)),2),
>I would expect .50
>However when I code in SQL 2005 Express manager against a SQL 2005
>database, the results display as 0.5000000000
>Using this
>round(Sev3Met/cast(Sev3Total as decimal(3,0)),2)
>I get 0.500000
>is there some setting I am missing? How can I get 0.50 '|||<wxbuff@.aol.com> wrote in message
news:1151530231.287935.312370@.m73g2000cwd.googlegroups.com...
> Say that the values of Sev3Met = 1 and the value of Sev3Total = 2
> Using this
> round((Sev3Met/(Sev3Total + 0.0)),2),
> I would expect .50
> However when I code in SQL 2005 Express manager against a SQL 2005
> database, the results display as 0.5000000000
> Using this
> round(Sev3Met/cast(Sev3Total as decimal(3,0)),2)
> I get 0.500000
> is there some setting I am missing? How can I get 0.50 '
>
Here are the rules for how the precision and scale of decimals is increased
through arithmetic.
Precision, Scale, and Length
http://msdn.microsoft.com/library/d...br />
8rc5.asp
David|||convert( decimal(3,2), round(Sev3Met/cast(Sev3Total as decimal(3,0)),2) )
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<wxbuff@.aol.com> wrote in message
news:1151530231.287935.312370@.m73g2000cwd.googlegroups.com...
> Say that the values of Sev3Met = 1 and the value of Sev3Total = 2
> Using this
> round((Sev3Met/(Sev3Total + 0.0)),2),
> I would expect .50
> However when I code in SQL 2005 Express manager against a SQL 2005
> database, the results display as 0.5000000000
> Using this
> round(Sev3Met/cast(Sev3Total as decimal(3,0)),2)
> I get 0.500000
> is there some setting I am missing? How can I get 0.50 '
>|||It actually does round it to two decimal places. It's just displaying extra
zeroes. Try this to confirm:
DECLARE @.Sev3Met NUMERIC(10, 5)
DECLARE @.Sev3Total NUMERIC(10, 5)
SELECT @.Sev3Met = 1.0
SELECT @.Sev3Total = 3.0
SELECT round((@.Sev3Met/(@.Sev3Total + 0.0)),2)
i.e., Make Sev3Total = 3 instead of 2. The result is:
.33000000000000000
The extra zeroes are a display issue.
<wxbuff@.aol.com> wrote in message
news:1151530231.287935.312370@.m73g2000cwd.googlegroups.com...
> Say that the values of Sev3Met = 1 and the value of Sev3Total = 2
> Using this
> round((Sev3Met/(Sev3Total + 0.0)),2),
> I would expect .50
> However when I code in SQL 2005 Express manager against a SQL 2005
> database, the results display as 0.5000000000
> Using this
> round(Sev3Met/cast(Sev3Total as decimal(3,0)),2)
> I get 0.500000
> is there some setting I am missing? How can I get 0.50 '
>

Decimal Data Type losing scale?

I'm trying to update a table that has decimal values. They are defined at
precision of 15 and scale of 2.
When I use a T-Sql update query, I'm sending a value "with pennies", but the
table is only reflecting the integer portion. I've even tried
UPDATE (myTableName)
SET myCost = CAST(@.Cost AS decimal(15,2))
without success.
When I step through the code of my VB.NET program and view the value of item
I've added to the parameters collection of my update query command object, I
DO SEE the pennies. When the query has executed, they aren't in the table.
I CAN TYPE the pennies into the record in the table with Enterprise Mgr. An
d
I can retrieve them with my program. But I can't send new values with
pennies and get them respected in the new table values.
I have seen in Books on Line that we are supposed to explicitly CAST our
decimal values. But shouldn't this take care of it?Hello Q,
When you run profiler what do you see the values being sent as? If you have
your precision and scale not matching exactly in your VB.NET application
it can cause it to send it incorrectly to the database.
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> I'm trying to update a table that has decimal values. They are
> defined at precision of 15 and scale of 2.
> When I use a T-Sql update query, I'm sending a value "with pennies",
> but the
> table is only reflecting the integer portion. I've even tried
> UPDATE (myTableName)
> SET myCost = CAST(@.Cost AS decimal(15,2))
> without success.
> When I step through the code of my VB.NET program and view the value
> of item I've added to the parameters collection of my update query
> command object, I DO SEE the pennies. When the query has executed,
> they aren't in the table. I CAN TYPE the pennies into the record in
> the table with Enterprise Mgr. And I can retrieve them with my
> program. But I can't send new values with pennies and get them
> respected in the new table values.
> I have seen in Books on Line that we are supposed to explicitly CAST
> our decimal values. But shouldn't this take care of it?
>|||Is it possible that you've failed to specify precision and scale
for your decimal parameter? The default precision and scale for
a decimal parameter is precision 18, scale 0. If this doesn't seem
to help, could you post the relevant VB.NET code dealing with
the parameter?
Steve Kass
Drew University
Q Johnson wrote:

>I'm trying to update a table that has decimal values. They are defined at
>precision of 15 and scale of 2.
>When I use a T-Sql update query, I'm sending a value "with pennies", but th
e
>table is only reflecting the integer portion. I've even tried
> UPDATE (myTableName)
> SET myCost = CAST(@.Cost AS decimal(15,2))
>without success.
>When I step through the code of my VB.NET program and view the value of ite
m
>I've added to the parameters collection of my update query command object,
I
>DO SEE the pennies. When the query has executed, they aren't in the table.
>I CAN TYPE the pennies into the record in the table with Enterprise Mgr. A
nd
>I can retrieve them with my program. But I can't send new values with
>pennies and get them respected in the new table values.
>I have seen in Books on Line that we are supposed to explicitly CAST our
>decimal values. But shouldn't this take care of it?
>
>

Dear group,

Dear group,
I would like to ask a brief question about NULL values and the IN operator.
The following SQL evaluates to (with ANSI_NULLS ON):
1 IN (1, 2, 3, NULL) --> Evaluates to true (makes perfect sense).
NULL IN (1, 2, 3) --> Evaluates to unknown (makes perfect sense).
4 IN (1, 2, 3) --> Evaluates to false (makes perfect sense).
ButK
4 IN (1, 2, 3, NULL) --> Evaluates to unknown and not to false.
I read that most major databases do this exactly the same and I actually
was able to find something about this behavior in the PostGreSQL
documentation (I couldnt find anything about this in the BOL, so I hoped
that the PostGreSQL might apply)
The docs state that for the IN operator:
If there are no equal right-hand values and at least one right-hand row
yields null, the result of the IN construct will be null, not false. This
is in accordance with SQL's normal rules for Boolean combinations of null
values.
So, why does SQL compare to a NULL value (if present) when no matching
values can be found for the right hand of the IN construct?
Kind regards,
Marcel
> 4 IN (1, 2, 3, NULL) --> Evaluates to unknown and not to false.
In this expression, we know for sure that 4 is not equal to 1, 2 or 3.
However, we can't say with certainty whether or not 4 is equal to or is not
equal to the unknown NULL value. The expression would be true if the
unknown value were 4 and would be false if the unknown value were 5.
Without certainty of the unknown value, standard SQL rules implemented by
various DBMS products return NULL rather than true or false.
Hope this helps.
Dan Guzman
SQL Server MVP
"Marcel van den Hof" <marcelvdh@.gmail.com> wrote in message
news:jetivog7pu0t$.jqeavwovs3nw.dlg@.40tude.net...
> Dear group,
> I would like to ask a brief question about NULL values and the IN
> operator.
> The following SQL evaluates to (with ANSI_NULLS ON):
> 1 IN (1, 2, 3, NULL) --> Evaluates to true (makes perfect sense).
> NULL IN (1, 2, 3) --> Evaluates to unknown (makes perfect sense).
> 4 IN (1, 2, 3) --> Evaluates to false (makes perfect sense).
> ButK
> 4 IN (1, 2, 3, NULL) --> Evaluates to unknown and not to false.
> I read that most major databases do this exactly the same and I actually
> was able to find something about this behavior in the PostGreSQL
> documentation (I couldnt find anything about this in the BOL, so I hoped
> that the PostGreSQL might apply)
> The docs state that for the IN operator:
> If there are no equal right-hand values and at least one right-hand row
> yields null, the result of the IN construct will be null, not false. This
> is in accordance with SQL's normal rules for Boolean combinations of null
> values.
> So, why does SQL compare to a NULL value (if present) when no matching
> values can be found for the right hand of the IN construct?
> Kind regards,
> Marcel
|||On Sat, 6 Aug 2005 19:22:01 +0100, Marcel van den Hof wrote:
(snip)
>The docs state that for the IN operator:
>If there are no equal right-hand values and at least one right-hand row
>yields null, the result of the IN construct will be null, not false. This
>is in accordance with SQL's normal rules for Boolean combinations of null
>values.
Hi Marcel,
This is from the PostGreSQL docs you mentioned, I presume?
This behaviour is in compliance with the ANSI standard. Dan has already
explained the rationale. There is only one minor mistake in the
PostGreSQL doc - the result of the IN construct with a NULL at the
right-hand side is not NULL, but UNKNOWN.
This distinction IS relevant. Null means "no valid data". Unknown is
valid data in three-valued logic.
Of course, the PostGreSQL doc is a thousand times better than the SQL
Server Books Online. BOL states:
"If the value of test_expression is equal to any value returned by
subquery or is equal to any expression from the comma-separated list,
the result value is TRUE. Otherwise, the result value is FALSE.
Using NOT IN negates the returned value."
And that is not a minor mistake - it is just plain wrong.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||> 4 IN (1, 2, 3, NULL) --> Evaluates to unknown and not to false.
Because we don't know the value represented by the NULL and therefore we
don't know whether 4 is in the list or not.
This is reasonably intuitive but ultimately you will search in vain for
satisfactory logic in SQL's handling of NULLs and three-value logic.
Consider the boolean expression:
(x=x) AND (y=y)
where x is NULL is y is non-NULL. The expected result is UNKNOWN, not TRUE.
This defies rational explanation. If the value of x is unknown then the one
thing we DO know for sure about x is that it is equal to itself! On the
other hand if the value x is deemed "inapplicable" then the comparison (x=x)
is surely a no-op and the rest of the expression should be evaluated without
it:
(y=y) = TRUE ... (but not in SQL).
Sorry, but the correct answer to your question is "because the SQL Standard
says so". :-)
David Portas
SQL Server MVP
|||Dan, Hugo and David thank you for your very clear and concise answers. You
have really helped me to improve my understanding of the three valued logic
and NULL values that are used in SQL server. A pity the BOL documentation
is somewhat inaccurate about these important matters.
If I want to further my understanding about these matters then I suppose
the best place for me is to study the ANSI SQL 92/ 99 standard?
Any links or pointers to relevant documentation (that is accurate ;-)) are
greatly appreciated.
Once again, thanks for the prompt reply to my question.
Kind regards,
Marcel van den Hof
|||Marcel van den Hof (marcelvdh@.gmail.com) writes:
> Dan, Hugo and David thank you for your very clear and concise answers.
> You have really helped me to improve my understanding of the three
> valued logic and NULL values that are used in SQL server. A pity the BOL
> documentation is somewhat inaccurate about these important matters.
I checked the SQL 2005 docs, and they are equally wrong. I submitted
a bug for this, although I believe it's too late for it to be fixed
for SQL 2005 RTM.
The bug is on
http://lab.msdn.microsoft.com/Produc...ckId=FDBK34083
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||On Sun, 7 Aug 2005 01:18:09 +0100, Marcel van den Hof wrote:
(snip)
>If I want to further my understanding about these matters then I suppose
>the best place for me is to study the ANSI SQL 92/ 99 standard?
Hi Marcel,
Not exactly. Studying the ANSI documentation is not a job for the faint
of heart. Seriously - they are written to define a standard, in as
concise a way as possible. They are not written to facilitate easy
understanding.

>Any links or pointers to relevant documentation (that is accurate ;-)) are
>greatly appreciated.
Most books are fairly accurate. Just keep in mind that all authors are
human, and humans can err. Also keep in mind that the more entry-level
books have to simplify things; books aimed at expert level will usually
present more of the fine details.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)