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!
It seems I am facing again an unsurmountable problem It should be so simple but one has to spend hours researching how to handle it. The MSDN help on this subject is increadibly obscure.
I have input parameters @months int, @days int, @years int in a stored procedure.
All I want to do is to get a DateTime variable out of them.
DECLARE @dated DateTime.
Thus I want @dated to be set to a DateTime value with month = @months, day = @days and year = @years. The MSDN help says that no CAST should be used since the conversion from int to DateTime should be implicit!!
No examples are given. They seem to show how to CAST or CONVERT varchar to DateTime. Shall I first convert my int to varchar?
It is rudiculous. I've tried dozens of variants. Please help.
Thanks.
My ERP software stores all dates as integers. So originally, I wrote a T-SQL function to convert these integer dates to normal people dates in the query I use as the recordset for my report. Well...that worked fine on 1,000 rows, but NOT for 100,000. So I've figured out that if I convert my normal person date parameter to an integer date, then SQL only has to convert my 1 parameter instead of having to convert 100,000 fields, (actually, 300,000 because I have 3 date columns).
So my question is, what is the best way to do this? This is what I have so far:
SET @Macola = Cast(Datepart(yy,@MacolaDate) as varchar) + Cast(Datepart(mm,@MacolaDate) as varchar) + Cast(Datepart(dd,@MacolaDate) as varchar)
However, I want the leading zeros for the month and day. For example if I enter '1/1/2004' into this function, it returns 200411, but I need it to return 20040101.
Any suggestions would be greatly apprectiated. Thank you.
Hi all,I have a problem converting datetime to integer (and than back todatetime).Depending whether the time is AM or PM, same date is converted to twodifferent integer representations, which holds as true on reversalback to datetime.AM Example:declare @DI integer; declare @DD datetimeset @DI = cast(cast('3/12/2003 11:34:02 AM' as datetime) as integer)set @DD = cast (@DI as datetime)print @DI; print @DDResult:37690Mar 12 2003 12:00AMPM Example:declare @DI integer; declare @DD datetimeset @DI = cast(cast('3/12/2003 11:34:02 PM' as datetime) as integer)set @DD = cast (@DI as datetime)print @DI; print @DDResult:37691Mar 13 2003 12:00AMNow, this is not a big problem if I knew that this is how it issupposed to work. Is this how SQL Server is supposed to work?
View 3 Replies View RelatedI know its a weird request, but we have created an application with sql server but our client wants a version which can be put onto disk.
We decided to create the stored procedures into queries, would this be the best idea and if so does anyone know if there is a freeware software that can do this or will I have to painstakingly re-create the queries?
Any advice would be greatly appreciated.
Many thanks
Smilla
Hi folks,I was working on MS sql server 2005 evalution where i have built a number of databases. However, i came to discove that the evalution version has expried before i finished my work. Now i have disinstalled the sql server 2005 and installed the Sql express edition.My concern here is how can i keep my databases so they can work with sql express edition?Thank you very much in advance.
View 1 Replies View RelatedI'm writing a stored procedure where one of the arguments (WHERE area) really only needs to be used in some circumstances. I.e., when the procedure is passed a USER_ID it needs to check that against the database, but in some instances I'll send 0 instead of a real USER_ID, and in those cases it should return all records regardless of the ID.
Here's what I've got:
...
and b.user_ID = CASE @user_ID WHEN 0 THEN '%'
ELSE @user_ID
...
...the problem being the '%' part. That won't work on an integer column.
Does anyone have any ideas here?
Thanks,
Al
The code below has this line
SET @SOGallons = @ODTGallons
I need it to add the Current value of @SOGallons to the newly selected value of @ODTGallons and set that as the new value of @SOGallons.
I've tried
SET @SOGallons = @SOGallons + @ODTGallons
SET @SOGalTemp = @SOGallons
SET @SOGallons= @SOGalTemp + @ODTGallons
Neither Worked
<CODE>
FROM [CSITSS].[dbo].[Orderdt] as ODT LEFT OUTER JOIN [CSITSS].[dbo].[Orddtcom] as OCOM
ON ODT.[Companydiv] = OCOM.[Companydiv] AND ODT.[OrderNumber] = OCOM.[OrderNumber] AND
ODT.[Sequence] = OCOM.[Sequence] WHERE ODT.[Companydiv]= 'GLPC-TRANS' AND ODT.[OrderNumber] = @OrdNum AND
([LineType] = 'IP' OR [LineType] = 'SO' OR [LineType] = 'DL' OR [LineType] = 'PU')
OPEN TC1
FETCH NEXT FROM TC1 INTO @LT, @ODTGallons, @ODTComm
WHILE @@FETCH_STATUS=0
BEGIN
IF @LT = 'SO'
BEGIN
SET @SplitTest = 1
SET @SOGallons = @ODTGallons
IF @SOGallons > 0
BEGIN
SET @SOGalTest = 1
END
ELSE
BEGIN
SET @SOGalTest = 0
END
IF @SplitTest <> @SOGalTest
BEGIN
SET @SOGalTest = 0
END
END
ELSE
BEGIN
SET @SOGalTest = 1
END
FETCH NEXT FROM TC1 INTO @LT, @ODTGallons, @ODTComm
END
CLOSE TC1
DEALLOCATE TC1</CODE>
I have a table which measures the changes in a feedback rating, measured by an integer. Most of my records are the same. Only the primary key & the timestamp change.
How do I query just the changes?
Example dataset:
idrating
15
25
35
45
56
66
[code]....
There are 20 rows & 5 changes. The query I want will result in just those that are different from the ones before them:
idrating
45
56
97
118
189
I use Microsoft SQL 2008
1
2
3
* (unscheduled visit) (should be 3.01)
* (unscheduled visit) (should be 3.02)
Basically when there is an unscheduled visit, it should take the previous visit number and add .01
I am not sure how to count using non integers
Thanks
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
View 5 Replies View RelatedHere is what I am trying to do...
I want to goup "members" togethers in a "group."
A table for members and a table for groups.
each containing coluns...
but inside Groups I would like a column that contains ID numbers for the members that be long to that group.
Being that members can belong to multiple groups - I can not use a GroupID in Members - if so I also need a way of it modular.
Obvisiously I am not a very good DB programmer - but I want the least amount of empty/unused space in my tables.
Hope this makes sense
I'm wondering if there is a function in SQL that works like SUBSTRING function but for integers. Like for example if I have a number like 20010112 and I want to cut it to the first for digits so that it reads 2001?
View 5 Replies View RelatedI am having difficulty trying to figure out how to compare two integers stored in a table to return a third. I have two integer fields in one table and two in another like this:
Table1.SomeNumber1 = 1
Table1.SomeNumber2 = 2
Table2.SomeNumber1 = 2
Table2.SomeNumber2 = 1
I need to be able to compare the first number from the first table to the first number in the second table. If the values are different I need to set a variable or field to 0. If the numbers are the same I need to set my variable or field to 1.
I need to follow the same procedure comparing the second number in the first table to the second number in the second table. In addition, I need to be able to do it in a single select statement.
Does anyone have any ideas on how this could be done? Thank you for any help you may be able to provide.
Gmz
New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
I need to run two reports each of A5 Size to run back to page and print on single A4 paper means in 1st half Sale bill will be printed and in second half Gate Pass Will Be Printed both report will be on same page and size and shape should be maintained. How to do it.
View 4 Replies View RelatedI'm embarassed I haven't figured this out already but here goes.
Lets say you need a percentage from dividing two integers for example
"select 2000/4000"
This will produce a zero and I'm assuming that is because of the datatypes involved (the values are coming from columns where the datatype is int)
I've tried converting the values to decimal types but I keep getting overflow erros unless I use very small values.
As always, thanks VERY MUCH for the kind advice.
I have a table with below data. Requirement is to replace all integers with continuous 6 or more occurrences with 'x'. Less than 6 occurrences should not be replaced.
create table t1(name varchar (100))
GO
INsert into t1
select '1234ABC123456XYZ1234567890ADS'
GO
INsert into t1
select 'cbv736456XYZ543534534545XLS'
GO
EXPECTED RESULT:
1234ABCxxxxxxXYZxxxxxxxxxxADS
cbvxxxxxxXYZxxxxxxxxxxxxXLS
drop table t1
-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
I'm having trouble adding a 4-byte integer with an 8-byte integer. Here's what I'm doing:
Column Name: BIG_ID
Derived Column: < add as new column >
Expression: (DT_I8)[ID] + 840230000538058
Data Type: eight-byte signed integer {DT_I8]
The error I get:
The literal 840230000538058 is too large to fit into type DT_I4. The magnitude of the literal overflows the type.
Then I try the expression:
(DT_I8)[ID] + (DT_I8)840230000538058
and
[ID] + 840230000538058
and get the same error.
What am I doing wrong? Is it possible to add 2 8-byte integers in regular expression? Why does it still think the literal is DT_I4?
Thanks,
Michael
Hello, all.
Why is it that, despite what is said in the sketchy SQL Help content, it appears to be impossible to cast a string to an integer in the Expression Builder to generate a value for a variable? More specifically, why does the following expression cause an error?
(DT_UI4) (SUBSTRING(@[User::FullDataPath], LEN(@[User:ataPath]) + 1, 2))
I'm iterating over files and using the name of a given file as an ID for an operation. I simply want to grab a file name using the Foreach Loop Container and process that file, while at the same time use the name in another operation. The file name will be something like "2.txt" (full path something like "c:somethingsomething2.txt"). I can use string functions to return the file name, which is a number as a string, and it should be no problem to cast that number as a string to a number (an Int32). SQL Server 2005 help has a chart that indicates such a cast is legal.
Maybe it's a crazy thing to be doing. Maybe I have to go about this a completely different way, but casting from "2" to 2 should be possible in the Expression Builder.
Thanks for any help.
I was told that, when possible, use integer fields for the equality comparison in INNER JOINS. Today someone suggested that using character fields that are indexed should be just as efficient. What do you think?
TIA,
barkingdog
I am working with a database named €œDocuments€? that contains 4 categories of text documents, each having its own number designation in an integer datatype column named SectionTypeId:
1 = Text
2 = Report
3 = Background
4 = Index
I would like to create a new column named €œDocType€? in which the integer data type for each document is replaced with a varchar data type letter (1 = T, 2 = R, 3 = B, 4 = I). I was able to easily create the new column and cast the data type from integer to varchar:
--CREATE NEW COLUMN €œDocType€? WITH VARCHAR DATATYPE
ALTER TABLE FullDocuments ADD DocType VARCHAR(1) NULL
Go
--UPDATE NEW COLUMN WITH CAST STRING
UPDATE FullDocuments SET DocType = CAST(SectionTypeID AS VARCHAR(1))
Go
But I have problems with the REPLACE method for replacing the numbers with letters. First I tried this based on the examples in MSDN Library:
--REPLACE NUMBERS WITH LETTERS
UPDATE Fulldocuments REPLACE (DocType,"1","T")
Which produced an error message: €œIncorrect syntax near 'REPLACE'.€?
Thinking that the datatype may be the problem, I tried this to convert to DT_WSTR data type prior to replace:
UPDATE Fulldocuments REPLACE ((DT_WSTR,1)DocType,"1","T")
Which produced the same error message: €œIncorrect syntax near 'REPLACE'.€?
I have never done a REPLACE before, so any suggestions for accomplishing this would be appreciated.
I have a table where each entry represents a range:
id, num_ini, num_fim
1, 1, 19
2, 20, 39
3, 40, 59
etc
Is there any way to select a recordset on this table with the following format?
id, num
1, 1
1, 2
1, 3
etc
2, 20
2, 21
2, 22
etc
I'm using MSSQL 2005
tks
I am trying to use bcp to load a text file into SQL Server 7. The decimal numbers in the text file are formatted as 123455 and when I load the values to SQL I want the last two digits to be the decimal 1234.55. My current process is loading the value as 123455.00. What do I need to do in the format file to get this to work???
View 2 Replies View RelatedHi all,
This is what I have:
DECLARE @OnTime int
DECLARE @UnControlled int
DECLARE @Volume int
DECLARE @GrossEffect decimal(10,2)
DECLARE @NetEffect decimal(10,2)
DECLARE @WeekEndDate datetime
SET @OnTime = (SELECT COUNT(DataID) FROM tblEDITempARS WHERE OnTimeFlag = 1 AND ARSScanType = 'D' AND ARSType='AN')
SET @UnControlled = (SELECT COUNT(DataID) FROM tblEDITempARS WHERE ControlFlag = 'U' AND ARSScanType = 'D' AND ARSType='AN')
SET @Volume = (SELECT COUNT(DataID) FROM tblEDITempARS WHERE ARSScanType = 'D' AND ARSType='AN')
SET @GrossEffect = (@OnTime/@Volume * 100)
SET @NetEffect = ((@OnTime + @UnControlled)/@Volume * 100)
SET @WeekEndDate = (SELECT DISTINCT WeekEndDate FROM tblEDITempARS)
INSERT INTO tblSummaryData2(ReportType, Volume, NetEffect, GrossEffect, WeekEndDate)
VALUES ('AN',
@Volume,
@NetEffect,
@GrossEffect,
@WeekEndDate)
My numbers are:
OnTime=8089
Uncontrolled=6
Volume=8095
The GrossEffect comes as 0 and the NetEffect=100
AN809501002/21/2004
Why is my GrossEffect = 0??? it should be 99.9
Hello, I have a table with a field with the format of 8 decimals. When I make a select to that table instead is given me (for example) 25,00 it gaves me 25,00000000. How can I fix the with the correct format? Thanks in advance.
View 2 Replies View RelatedI need to display decimal results from SQL Server queries, but the decimals keep getting truncated. For example, 9 divided by 4, keeps getting truncated to 2. Does anyone know how to display the whole result, including the decimals?
View 2 Replies View RelatedI have a field in my SQL table that is defined Decimal(10,8), which
provides me with the ability to have up to 8 digits after the decimal
place, however, if I store a value such as 3.14, it is stored as
3.14000000. This is fine in the database, but it is the same when it's
returned. I have tried using Convert.ToDecimal on my returned row but
it doesn't work. The value at runtime appears as 3.14D in the locals
window but displays with all the insignifcant digits.
Any ideas what's happening or if there is an easy way to trim the trailing zeros?
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!
Rolf
in quantity column - i have
100.00000
but i want only 100
no decimals..
how to get that?
thanks
I have two fields whose format in the table is decimal (14,5).
I now have to divide one field by the other, and I want the result
to be in the format decimal (21,16).
Whatever I do I'm not able to get this result with sixteen decimals.
I tried with dec(Field1,21,16)/Field2 but it yields six decimals.
I tried with dec(Field1,21,16)/Field2 but it yields an integer.
I tried other ways but I always get a result which is made of maximum
six decimals.
What can I do?
Thank you.
Anna - Verona (Italy)
I am relatively new to SQL server. I am tring to send some decimalvalues to the database using a stored procedure with parameters of typeDECIMAL. Every time it inserts the values into the database thedecimals are truncated. I saw on the MSDN library that you have to setthe precision and scale values b/f you run the stored procedure. So Iset the precision to 8 and the scale to 4 and it still didn't help. Cananyone help me?
View 6 Replies View RelatedHello,I am hoping you can help me with the following problem; I need to process the following steps every couple of hours in order to keep our Sql 2000 database a small as possible (the transaction log is 5x bigger than the db).1.back-up the entire database2.truncate the log3.shrink the log4.back-up once again.As you may have determined, I am relatively new to managing a sql server database and while I have found multiple articles online about the topics I need to accomplish, I cannot find any actual examples that explain where I input the coded used to accomplish the above-mentioned steps. I do understand the theory behind the steps I just do not know how to accomplish them!If you know of a well-documented tutorial, please point me in the right direction.Regards.
View 2 Replies View Related