Diff Between Money And Smallmoney
Mar 15, 2008What is the difference between money and smallmoney, and is there a way to format these?
View 3 Replies
ADVERTISEMENT
When To Use Money, SmallMoney, Decimal
Aug 27, 2004Could someone explain to me what the best practices are for using these three data types? (i.e. when to use them). I thought this would be a simple answer to find, but after looking through books on line, and this forum, I have still not found the answer. Can anyone help?
View 1 Replies View RelatedWhy Does The Sum Of A Smallmoney Have Money Datatype?
Jul 3, 2007Using the following script...
create table a
( policy_id int not null,
amount smallmoney not null);
insert a values (1, 100.0);
select policy_id, sum(amount) sum_amount
into b
from a
group by policy_id;
Why is the datatype, of column sum_amount in table b, money rather than smallmoney?
Select Data From Diff. Tables On Diff. Servers
Feb 12, 1999I need to write a 'select' statement to fetch data from different tables, which are located on different servers.
Can any one help in writing this 'select' statement with out moving the tables on to same server.
Thanks in Advance.
Murali Raparla.
How Can I Cast A Money Field In A View To Look Like Money
Oct 16, 2007I have a special need in a view for a money column to look like money and still be a money datatype. So I need it to look like $100.00 (prefered) or 100.00(can make work).
If I convert like this '$' + CONVERT (NVARCHAR(12), dbo.tblpayments.Amount, 1) it is now a nvarchar and will not work for me.
How can I cast so it is still money? by default the entries look like 100.0000.
They must remain a money datatype.
Decimal/smallmoney
Apr 27, 2007Hello all,
I have two values (1.0 and 0.50 that need to be saved into SQL Server 2005 as numeric values. My issue is that when I try to save 0.5 to the table it is rounded up to 1.0. I checked the Stored Procedures and it looks good there. I tried the datatypes decimal (2, 1) and small money but both cause the value to round. Can anyone help me save the value as 0.5 to the table?
Thanks in advance!
Smallmoney With 2 Digits
Jan 2, 2008I have a table for products and the price field is smallmoney data type. When I view the data stored it shows it with millionths. How do I get it to store only hundredths?
View 2 Replies View RelatedHelp! SMALLMONEY:Too Many Decimal Places
Aug 12, 2000Hello all...
I am new to SQL 7, and am having trouble with the money and smallmoney data-types. When I pull data from these columns, it has four decimal places, and I need only two (four is screwing the perl code up). What do I do now?
All help much appreciated!
String To Smallmoney - Sql2000
Jul 18, 2003using SQL2000, table has a field defined as 'smallmoney'. This is being populated from a text file that has two digits after the decimal -> 56.50
I was trying to use cast and convert going in but no luck, as near as I can determine they only work on output.
So the question is : how can I get string data into smallmoney. Otherwise I have to redefined the field as a varchar or decimal
Comments?
Thanks
RK
Smallmoney Data Type
Mar 25, 2007whenever i add values in my price column, it automatically put 4 decimal places. how will i limit to 2 decimal places of this smallmoney datatype?
View 4 Replies View RelatedStore Procedure Problem W/smallmoney
Feb 15, 2004Hello,
I am having a problem with a stored procedure and inserting money value into a smallmoney field. I have tried a few different lines but still get errors. This is a sample of the code below and the problem is with the MenuCost and converting a string to smallmoney. I am not having any luck with. Thanks inadvance for any advice.
Gary
Dim MenuName As String = txtName.Text
Dim MenuDescription As String = txtDescription.Text
Dim MenuCatId As String = DropDownList2.DataValueField
Dim MenuTypeId As String = DropDownList1.DataValueField
Dim MenuCost As String = txtCost.Text
Dim ConnectionString As String = fdfadsfdsfasdfsdfsdf
Dim CommandText3 As String = "ins_MenuItem"
Dim myConnection3 As New SqlConnection(ConnectionString)
Dim myCommand3 As New SqlCommand(CommandText3, myConnection3)
Dim workParam As New SqlParameter()
myCommand3.CommandType = CommandType.StoredProcedure
With myCommand3.Parameters
.Add("@MenuName", SqlDbType.Varchar, 150, MenuName)
.Add("@MenuDescription", SqlDbType.Text, MenuDescription)
.Add("@MenuCatId", SqlDbType.Int, MenuCatId)
.Add("@MenuTypeId", SqlDbType.Int, MenuTypeId)
'.Add("@MenuCost", SqlDbType.SmallMoney,4,System.Data.SqlTypes.SqlMoney.Parse(MenuCost))
'.Add("@MenuCost", SqlDbType.SmallMoney,4, Convert(MenuCost AS Varchar))
'.Add("@Amount", SqlDbType.SmallMoney,4,System.Data.SqlTypes.SqlMoney.Parse(Amount))
'.Add("@MenuCost", SqlDbType.SmallMoney,4,System.Data.SqlTypes.SqlMoney.Parse(MenuCost))
End With
myConnection3.Open()
Page 2 - String To Smallmoney - Sql2000
Jul 21, 2003the placeholder approach works nicely with an insert statement and I've tried it with an update statement but keep getting an error. This is the current code:
Var1 = strArray(1)
Var2 = strArray(2)
Var3 = (strArray(3)
Cmd.CommandText = "UPDATE WBS.dbo.pludec38 SET PLU = ?, SET Dscrpt1 = ?, SET Price = ?"
Cmd.Parameters.Refresh
Cmd.Parameters(0) = Var1
Cmd.Parameters(1) = Var2
Cmd.Parameters(2) = Var3
Cmd.Execute
The error occurs at or near the refresh stmt. What am I doing that is causing this? And what is the difference between this and the insert. The insert uses the parameters block without choking.
Thanks
RK
Problems Converting Varchar To Smallmoney
Oct 11, 2006Hi
My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.
The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)
As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.
What do i have to do to convert the value as it is entered by the user?
Thanks in advance
t.
Statement:
INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
{4}),{5}, getdate()
DDL for concerned database:
CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO
Inserting Data Into Database Field SmallMoney
Jun 5, 2007what i understand if if the data field is integer or money, not string, then i need to do a convert(datatype, value) in the insert but how come its still not working INSERT INTO [Product] ([Title], [Description], [Processor], [Motherboard], [Chipset], [RAM], [HDD], [OpticalDrive], [Graphics], [Sound], [Speakers], [LCD], [Keyboard], [Mouse], [Chassis], [PSU], [Price]) VALUES (@Title, @Description, @Processor, @Motherboard, @Chipset, @RAM, @HDD, @OpticalDrive, @Graphics, @Sound, @Speakers, @LCD, @Keyboard, @Mouse, @Chassis, @PSU, convert(smallmoney, @Price))
View 1 Replies View RelatedDate Diff
May 14, 2007I need to return the number of min from a table I am using the following query. But it gives me an error "Msg 241, Level 16, State 1, Line 1Syntax error converting date time from character string". can someone please help.SELECT DateDiff(Mi, CAST((SCHDATE + ' ' + SUBSTRING(SCHTIME, 1,2) + ':' + SUBSTRING(SCHTIME, 3,4)) AS DateTime), CAST((ACTDATE + ' ' + SUBSTRING(ACTIME, 1,2) + ':' + SUBSTRING(ACTIME, 3,4)) AS DateTime)) AS StopMinutes, BACPY, BARTRM, BAORD, BSAPOR, BABLN, BSASSQ, BSACNO, CSTRDATA, BSASCY, BSASST, TTLREV, SHAALP, SCHDATE, SCHTIME, ACTDATE, ACTIME, OQTCOD, BAADES, PCS, WGT, Tractor, Driver FROM dbo.JCI_Delivery_Report
View 3 Replies View RelatedDiff B/w Two Dates
Dec 11, 2000Hi,
i have 3 fields: start_inspect_datetime, end_inspect_datetime, Diag_Hrs.
so i want to get the difference of start and end datetime=Diag_Hrs.
here i am using the below stored proc.
but i am getting only the hours or minutes or seconds.
so how to get the hours(if diff>59 mins),minutes(if diff>59 sec),seconds.
for Ex: here diff=185 sec. then Diag_hrs should be 3 hours,0 mins, 5 secs.
so how we'll get this.
pl help me out asap.
Thanx
reddy
select Asset_Diag_Hrs= DATEDIFF(hh,start_inspect_datetime,end_inspect_dat etime) from asset_diag_trans_table
--where Gpc_no=@GPC_no
--select Asset_Diag_Hrs=(datediff(mm,start_inspect_datetime ,end_inspect_datetime)) from asset_diag_trans_table
-- where Gpc_no=@GPC_no
select Asset_Diag_Seconds=(datediff(ss,start_inspect_date time,end_inspect_datetime) ) from asset_diag_trans_table
where Gpc_no=@GPC_no
Diff. Backup
Oct 24, 2000Can i restore a diff. backup alone without a complete backup?
(what i am trying is this.....there are two different servers at two diff. places....i need to have both servers in sync. at all times.modifications will take place in one server and the modifications have to be reflected in the other server.i could not go for replication as the servers cannot be connected.sending complete backups daily will be a overhead .hence planning to take complete backup once and send diff. backups alone on subsequent days to the other server.
how can i achieve this?)
Diff Backup Help
Mar 22, 2006Dear all
I am new to the MS SQL, my problem is as follows.
I am having a online database on sql 2k.
every 15 days we have to give payout from our system, so we
have a offline server in our office, we take the complete backup of that day & restore the same on the offline server,
& start the payout process.
The problem is that the full backup is a big file & take a lot of time for downloading from online server.
is it possible that we take on diff. backup of that day & will restore the same on the offline server so the file will take less time to download.
but my offline backup is 15 days old, will that update all the records or not?
regards
Abhijit
Database Diff?
Jan 6, 2004I have 2 databases:
1 from production and 1 from development.
None of the developers kept a changelog so i need to know what has changed (or what is different between the 2).
Any Ideas on how to do this?
Diff Backup
Jun 11, 2008In playing with differential backups, i have taken a full last night, 17GB, took my first diff today 16.5GB, took another diff right after, it was still 16.5GB, took another diff right after 16.5 GB. This database has 0 activity during the day, the import takes place @ night, and select's are done to it during the day. Any help would be greatly appreciated
View 3 Replies View Related3 Fks Extrct Frm 1 Tbl Into One Row On Diff Tbl
Nov 20, 2007Hey guys,
m new to SQL i mean really new so i appreciate all the help i can get on this as soon as possible.
Is it possible to extract 3 foreign keys from a single table into one record/row on a different problem ?
Thnx guys hope to hear soon
Diff Between SQL 2005 RTM & SP2
Nov 29, 2007Hello,
I wanted to know the difference between SQL Server 2005 ( RTM) Version and the regular SQL Server 2005 SP2. And does the RTM version have any service packs.
Thanks
Database Diff?
Jun 6, 2007Are there any tools/utilties that can diff 2 sql server databases. I know the DB Pro Edition of VSTS can do it, but its hard to justify a jump from my $800 copy of VS2005 Pro to a $5k-6k version of VS just to get database diff. The tablediff looks promising but I need it to do SPROCS and SFUNCS too. Any helps or recommendations would be appreciated.
View 4 Replies View RelatedDiff Between Float And Real
Jan 7, 2008Hi All,
What is the difference betwen real and float datatype of SQL server.
Please one exapmle for each.
The real datatype is like a float except that real can only store numbers.What does it mean: float can store even characters.
Thanks
Abdul
Diff Result Between SQL 7 && SQL 2000
Feb 4, 2004Hi.
When I execute the following sql statement in SQL 2000 DTS or query analyzer, I received the following error message. I used [ ] because my field names have spaces in between.
The same sql statement ran perfect in SQL 7. What could be wrong? Please advise. Thanks a million.
Update SAPvsSQL set [Sales Organisation] = sales_org,
[Value Out By] = val_diff, [Qty Out By] = qty_diff
Error message:
Server: Msg 16882, Level 11, State 1, Procedure sp_runwebtask, Line ....
SQL Web Assistant: Web task not found. Verify the name of the name for possible errors.
Best regards
Diff Between Numeric And Decimal
Apr 12, 2008Hi, I need to know the difference between Numeric and Decimal datatypes. In which we case we use numeric and in which case we use decimal? I searched in some sql related websites and came to know that both are same. Then what is the need of these two datatypes? Either Numeric or Decimal is alone enough? Please explain me in detail ....
Awaiting your replies...
Thanks,
Rakesh.
Anyone Compare The Differences BTN DIFF SQL
Dec 6, 2005HEY,
CAN YOU TELL ME THE DIFFERENCES BETWEEN VARIOUS SQL
Diff Between Varchar And Nvarchar
May 22, 2006hi
could any one help me in differentiating between varchar and nvarchar
Thanks in advance
Need Help Troubleshooting Diff BackUp Job
Jan 3, 2008I have a handful of jobs that I need to troubleshoot, but this one will hopefully give me enough insight to do the rest myself. I need to figure out why a Maintenance Plan that performs a Diff Backup on 4 databases, and then a transaction log back up on the 4 databases is failing. I just get the following error message, and I cannot tell what failed.
Executed as user: <SERVER_NAME>SYSTEM. ... 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:20:05 AM
Progress: 2008-01-03 11:20:05.95
Source: {SOME_STUFF_WAS_HERE}
Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete
End Progress
Progress: 2008-01-03 11:20:06.35
Source: Back Up Database (Differential)
Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete
End Progress
Progress: 2008-01-03 11:20:06.35
Source: Back Up Database (Differential)
Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete
End Progress
Progress: 2008-01-03 11:20:06.37
Source: Back Up Database (Differential)
Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete
End Progress
Progress: 2008-01-03 11:20:06.37
Source: Back Up Database (Differential) ... The package execution fa... The step failed.
How can I find out how to fix this problem? And please let me know if I am doing something wrong. (For instance, perhaps those two tasks shouldn't be together.)
- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/
Trying To Group By Two Diff Criteria
Feb 16, 2007I have a table t1 with two columns : c11 varchar(32) , c22 varchar(32)The data in the table is :'11', 'aa01'and on upto'11', 'aa50' : total 50 entries'22', 'b01''22', b'02''22', b'03''33', 'c01' to '33', 'c40' : total 40 entries'44', 'b02''44', 'd01''44', 'd01''44', 'd01'How can write a query which will bunch together values of c11with rows 5, and then bunch together values of c11 withrows < 6, and add them up.My output should be :'11' 50'33' 40'others' 7 (3 rows for '22' and 4 for '44' are bunchedtogetheras the # of rows < 6, and added. 3+4 = 7)
View 1 Replies View RelatedNeed Help In Matrix To Find % Diff.
Jul 31, 2007
Country1
Country2
2006
2007
% diff
2006
2007
% diff
Segment1
80
90
12.50%
60
50
-16.67%
Segment2
150
120
-20.00%
80
100
25.00%
Total
230
210
-8.70%
140
150
7.14%
I need a to build a result as shown above in matrix, many countries will be in resultset and each country performance is evaluated based on previous year. (90-80)/80=12.50%. the problem is this year and segment values are calculated based on aggregate functions and is derived by means of specific formaule. for country1 in year 2006 for segment1 the value 80,90 is arrived by using sum(leadtime*Ordervalue)/sum(Ordervalue).
So i cannot store these current and previous year values for calculating the % bymeans of custom code, runningvalue cannot be used as the formulae already has an aggregate, is there any direct way to locate the cell references just like excel R1C1 with matrix? if so then i can refer the cells for calculating my %. please help.
Thanks in advance.
brinda
[help] Possible To Script A Diff Into SQL Query?
Sep 11, 2007My goal is to add a diff into a query that grabs data from 2 different tables.
The code:
SELECT
MIN(TableName) as TableName,
ID1, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8
,COL9, COL10, COL11, COL12, COL13, COL14, COL15, --COL16,
COL17, COL18, COL19, COL20, COL21
FROM
(
SELECT 'Table A' as TableName,
SessionID as ID1,
StartDateCode as COL1,
StartTimeCode as COL2,
EndDateCode as COL3,
EndTimeCode as COL4,
HandledByCode as COL5,
DispositionCode as COL6,
DNISCode as COL7,
CallServiceQueueCode as COL8,
ApplicationCode as COL9,
IVREndPointCode as COL10,
BankCode as COL11,
TotalQueueTimeSS as COL12,
TotalAgentTalkTimeSS as COL13,
TotalAgentHoldTimeSS as COL14,
TotalAgentHandleTimeSS as COL15,
--TotalIVRTimeSS as COL16,
AfterHoursFlag as COL17,
SourceSystemID as COL18,
anubisTransferExtNumber as COL19,
anubisEndPoint as COL20,
AccountNumber as COL21
from [pdx0sql45].Rubicon_Marts.dbo.INB_Call_Fact
where startdatecode between 2738 and 2769
UNION all
SELECT 'Table B' as TableName,
SessionID as ID1,
StartDateCode as COL1,
StartTimeCode as COL2,
EndDateCode as COL3,
EndTimeCode as COL4,
HandledByCode as COL5,
DispositionCode as COL6,
DNISCode as COL7,
CallServiceQueueCode as COL8,
ApplicationCode as COL9,
IVREndPointCode as COL10,
BankCode as COL11,
TotalQueueTimeSS as COL12,
TotalAgentTalkTimeSS as COL13,
TotalAgentHoldTimeSS as COL14,
TotalAgentHandleTimeSS as COL15,
--TotalIVRTimeSS as COL16,
AfterHoursFlag as COL17,
SourceSystemID as COL18,
anubisTransferExtNumber as COL19,
anubisEndPoint as COL20,
AccountNumber as COL21
from pdx0sql04.Rubicon_Marts.dbo.INB_Call_Fact
where startdatecode between 2738 and 2769
) tmp
GROUP BY ID1, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8
,COL9, COL10, COL11, COL12, COL13, COL14, COL15, --COL16,
COL17, COL18, COL19, COL20, COL21
HAVING COUNT(*) = 1
ORDER BY 2,1
---------
Is it possible to add a command into the query to output diff/compare scenario?
Thanks in advance for any help.
DB Backup From 1 PC && Restore To Diff. PC
Aug 8, 2007Friends,
I have taken backup of a database, of which Data (MDF) & Log (LDF) files are located on "E:...." directory.
Now, I am using the same backup file to restore on another PC which is having no partitions at all. I mean now I have to restore the database using same backup file, of which Data & Log files should be located on "C:...." directory only, as it has no partitions.
This restore process gives me error :
Restore failed for Server 'HSVMMICROFIT'.
Details : System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:Program FilesMicroFitHealthStar v6.0 Server EditionDataMICROFIT_HealthStar_Data.mdf'.
What is to be done, in order to restore the database from the same back up file successfully, regardless whether the original physical location of the MDF & LDF file, is existing on current PC ??????????????
Please try to solve the same for me.... W8ing...