Keeping Decimal Value

May 23, 2008

Hi, I have the following code in a query:

SELECT [Issue date],DATEDIFF("dd",[Issue date],[Start date])/365 AS runningdays
FROM Database1..[Insurance Policies Working DB]
WHERE [Policy Number] LIKE '%1368529%'

The part 'DATEDIFF("dd",[Issue date],[Start date])' comes out as 364 if calculated on its own. However, then when it is divided by 365 it comes out as 0. How do I get it to show as a decimal instead of just rounding it down automatically? (Hope I've made sense)

Thanks in advance

View 3 Replies

Legacy Database Uses Decimal Data Types.--&> AutomobileTypeId (PK, Decimal(10,0), Not Null) Why Not Integers Instead ?

Sep 26, 2007

I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?

Example: AutomobileTypeId (PK, decimal(10,0), not null)

View 5 Replies View Related

Data Type With Decimal Point For Decimal Values But Not For Whole Integers

Dec 8, 2013

I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?

View 2 Replies View Related

Cast Or Convert Nvarchar With Comma As Decimal Separator To Decimal

Apr 29, 2008

Hello.

My database stores the decimals in Spanish format; "," (comma) as decimal separator.

I need to convert decimal nvarchar values (with comma as decimal separator) as a decimal or int.

Any Case using CAST or CONVERT, For Decimal or Int gives me the following error:

Error converting data type varchar to numeric

Any knows how to resolve.

Or any knows any parameter or similar, to indicate to the Cast or Convert, that the decimal separator is a comma instead a dot.

View 5 Replies View Related

Converting (casting) From Decimal(24,4) To Decimal(21,4) Data Type Problem

Jul 24, 2006

Hello!

I would like to cast (convert) data type decimal(24,4) to
decimal(21,4). I could not do this using standard casting function
CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable)
because of the following error: "Arithmetic overflow error converting
numeric to data type numeric." Is that because of possible loss of the
value?

Thanks for giving me any advice,

Ziga

View 6 Replies View Related

How Can I Use The Decimal Comma Instead Of Decimal Point For Numbers In Jet Engine?

Sep 19, 2007

I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places.
But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted.
How can I solve or get around this problem? (with jet if it possible)
iviczl

View 4 Replies View Related

Converting Decimal To String W/O Decimal Point

Jul 23, 2005

I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?

View 6 Replies View Related

Converting Decimal Point To Decimal Comma

Nov 30, 2007

Hi all,

I am designing some reports for a German branch of my company and need to replace decimal point with a comma and the thousand comma seperator with a decimal point.

e.g.
��1,500,123.00 to ��1.500.123,00

Is there a property that I can change in the report designer to allow this to happen or is this something I need to convert in a Stored Proc.

Any help would be much appreciated

Thanks!

View 5 Replies View Related

Decimal Limited To 4 Digits To Right Of Decimal?

Jun 18, 2007

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

View 3 Replies View Related

Packed Decimal To Decimal Conversion

Jun 4, 2007

Hi,

I am having a file in which amount fields are given in a Packed Decimal format. Can anyone suggest me how I can read this data element from the file and convert it into SQL decimal datatype.

File is a fixed length. All the amount fields are given in Packed Decimal Format and rest of the fields are given in text format.
How can i identify and convert only those packed decimals using SQL/.Net.

Example : a row in a file that has some packed decimals
158203508540188236252EUR20BZK0030 œ&
20060715 0001010100010101

Please help!

Thanks

Mirudhu

View 4 Replies View Related

Keeping Job History ...

Aug 26, 2002

SQl7, sp3, NT4

How do I keep th job history of a job, say if I re-create the job?

We recreate the jobs often as part of a code move, but I'd like to retain the history of the previous jobs?

** sp_help_jobhistory -- only shows the jobs that exist, and not old jobs that no longer reside on the server.

Thanks,
AJ

View 1 Replies View Related

Keeping 2 DBs In 'synch'

Jan 10, 2005

Hi all, here are my goals: Have the same DB on two different stand-alone computers, and keep them up-to-date from each other.

Basically a user would input to a DB for a week. Then every week or two, update the other stand alone DB with the new input. The DB would be exactly the same.

What are my options for this? I'd like it as easy as possible! Are there any software packages that deal with this type of transfer, etc.? Thank you!

View 5 Replies View Related

Keeping Last 10 Entries By ID

Sep 24, 2006

Hello

my table :
Report :
R_id (PK)
RName
RDate

i am having a few 10.0000 lines and i want to keep the last 10 (or less if not in the table) rows maximum for each name

i can have 100 report by name (100 rows with the same name and of course R_id and RDate are different)

how can i do it ?

thanks a lot for helping

View 1 Replies View Related

Keeping Tables Sorted

Nov 28, 2003

we have a simple table

Key, Name, Address, City, State, Zip ................ect

I would like to keep this table sorted by Name, theirfore I won't have to sort my results with every querry.

I think I need to add something to my insert to tell my table - "Hay take Jones", open up the prober place and stick him in the proper spot.

Ex: We have Appleby and Robertson in our table now. My insert would tell SQL Server to take Jones, figure our where he belongs (alpha), and stick him in, resulting in.

Appleby
Jones
Robertson

This way I wont have to as the querry to sort stuff every time I reference this table, this will save lots and lots of overhead. and help keep my clients happy with quick(er) response.

thanks in advance -arthur

View 3 Replies View Related

Keeping Track Of Table Changes ...

Nov 7, 2001

Hi

We need to keep track of all changes that are made to our tables.

The changes will be saved in a table that records:

- the table in which the change was made
- the name of the field that was changed
- the old data for the field
- the new data for the field etc..

I've seen a few examples that record the name of the table that was
modified but none that record done to the field level.

Can anybody give some guidance?

Thanks..

Wayne

View 1 Replies View Related

Keeping A Lock On Table Or Row

Feb 5, 2004

I need to update a row but keep a lock on the table (so no one else can update it) while I do run some more code. In Oracle, it always locks whatever you update until you hit commit, but sql server works opposite. How do I tell it not to commit a statement, or how would I explicitly get a lock and then release it later?

View 4 Replies View Related

Keeping Breakpoint And Debugging

Feb 27, 2007

hi, is there any possiblity to keep break point and run the step by step compilation as like we do in .net,if means please telll me how to do?

View 3 Replies View Related

Help With Keeping Track Of Payment

Nov 13, 2007

Hello All,

I have a problem concerning keeping track of a value within a query.
I have a table that tracks invoices recieved and payments made.
For each invoice number there may be multiple payments made against it.
I need something that will check and make sure that each invoice number has its payments equal to its received amount.

Any help would be greatly appreciated.
Thanks,

View 5 Replies View Related

Keeping Database As Small As Possible ?

Oct 16, 2006

Hello everyone,

I have a winform application with C# front end and sql express 05 backend.

In this database i have a table that holds manufacturer provided pricing and the manufacturers we work with update pricing constantly.

We have one table called "manufacturerpricing" which we are constantly inserting and deleting pricing records to/from to keep manufacturer pricing up to date. We may insert and delete as many as 2,000,000 records per month into this table.

This works perfectly fine and we have no problems here at all.

But with that being said, I am worried about the size of the database growing out of control due to temporary space etc. The database just keeps getting bigger and bigger.

How do I run some maintenance to keep the database size under control.

I would like to run this automatically from the C# front end so if ther is a stored proc I can call or an C# assembly I can reference that would be ideal.

Any help is greatly appreciated.

View 3 Replies View Related

Keeping State Between Calls To A UDF

Dec 14, 2006

Hi,

I'd like to keep state between calls to a UDF (mainly for caching purposes). I can shove an object into the appdomain using SetData and read it using GetData, but that requires the assembly to be set to UNSAFE. I'm confident I can secure the DB and the assembly fairly well, but I like defense in depth, and if there's another way to save state between calls to a UDF, I would prefer those.

Is there another way to store state between calls to a UDF, without putting data into DB tables or using things that will require the assembly to have such a wide permission set?

Thanks,

Alex

View 4 Replies View Related

Recomendations For Keeping Track Of Changes

May 8, 2007

Let's say I develop a 'version 1' of a program/database and ship it to some customers.

Then I continue work on version 2 with some databasechanges etc.

When version 2 is shipped to the customers, they need a way to upgrade their database

from version 1 to version 2. I guess this is easiest done with one/several sql-scripts.

What I want is your opinion about the best way to keep track of the changes between

version 1 and 2. Here are two ways with pros and cons I've thought of:

1 - Keep manual track of every change (index, changed columns, relations etc) and update a scriptfile with all changes.

Pros: Full control of what is happening

Cons: Pretty much extra work and risk of missing some changes.

2 - When version 2 is ready, run some third party tool to get a diff between the databases

Pros: Fast and easy

Cons: The DB-changes may need to be applied in a special order, with default values, etc etc and I'm not sure all tools handle this in a good way...?

So... what is your recomendations? I'm sure this must be a headace in every development project?

Regards Andreas

View 13 Replies View Related

Keeping Dirty Data

Jan 24, 2008

This may be more of a data design question and not an ssis question, but figured folks here could have a good idea.....the organization I'm in has the business need of collecting data from outside organizations and tracking what data is bad and what data is good. When I say bad data I mean everything from things outside of range to absolute *** - characters in integer columns, integers in character columns, special characters, etc. The data comes in in the form of flat file so it's a free for all until it hits ssis & the db engine.

Eventually of course they work to get the data corrected at the source & resubmitted but in the meantime, they have the legitimate need of not only pushing the data into the database (dirty or not), but keeping all the bad stuff. I can't in good conscience make everything a varchar to catch everything - that would go against the database gods. IMO - I still must make an integer be an integer , characters are characters, etc. But what do I do with the junk? Any thoughts?

View 4 Replies View Related

Keeping An Accurate Count Of Events

May 16, 2006

I am newish to databases and would appreciate some
advise. I think I have a solution to my
problem but it is going to take me a lot of time to get it running. If there is a better way of doing it I would
like to know.

I have a table :-

“eventDates�? with
columns (id, date, eventID,
eventCount)

The id auto increments as a Primary Key.
date holds the date of the
event.
EventID references another
table with info about the events
Up to 9 eventIDs can be added
for each date and I want eventCount to hold an integer (1 to 9) to allow me to “pivot�?
the data to the table below

“results�? with columns (date,
eventCount1, eventCount2 …..eventCount9) so each row will hold a date and non
to nine eventIDs occurring on that date.

Is there an easy way to keep eventCount accurate or do I
just have to write a lot of code? I
will need to be able to remove events as well as add them. I will use a mixture of stored procedures
and VB.Net I guess?

Many thanks for any advice.

Mike

View 4 Replies View Related

Keeping DTS Return Messages In A Log Table

Jul 2, 2001

Hi, I am trying to find a way to capture all the status (Start time, execution time, Status messages etc) from executing a DTS package in to a table I will create in a database, does anyone know, where those information being kept?
When I excute the DTS package manually, a window will come up and show the status of each step within the DTS package. I am hoping to capture these information and load it to my log table.

Thanks in advance.

View 1 Replies View Related

Keeping Entire Table In Memory

Aug 17, 1999

How can I instruct SQL Server to keep entire table in memory? ie the memory pages should not be swapped to HD.

View 2 Replies View Related

Keeping Relationship Settings On Export

Sep 19, 2000

I'm exporting a large database. In enterprise manager the settings on
all of the PK/FK relationships are that
"Enable relationship for Insert AND Update" is UNchecked.
I need it this way, so I can delete and insert to the tables
without being hassled by THE MAN.

When I export the database, using DTS I export all the objects (EVERYTHING),
and all the data too. When I open the freshly copied database and get
properties on any relationship "Enable relationship for Insert AND Update"
is CHECKED! ARGH!

How do I keep this from happening? I'm so frustrated.
It is very time consuming to uncheck that darn box on hundreds
of relationships. Why doesn't it just stay the way it is set in
the original source DB ??

Is there a way to export a database and keep it EXACTLY the same?

If anyone can help me with this it would save me dozens of hours in work.
Thanks in advance.

Josh

View 10 Replies View Related

Log Trigger - Keeping Track Of Status Changes

Aug 28, 2015

I have a trigger that keeps track of status changes...

IF UPDATE(STATUS)
BEGIN
DECLARE @currentdate datetime
DECLARE @currentstatus integer
DECLARE @UserID integer
DECLARE @PermitID integer
DECLARE @Status integer

[Code] .....

It works but not the way I want it to. The @currentstatus and @newstatus are the same. I want the status before and after the update. I asked around as to how to do this and some one told me to use the Deleted table.

View 3 Replies View Related

Keeping A Production And Test Db Insync

May 14, 2004

I have just finished upsizing an Access database to SQL Server 2k. Now the SQL Server need to be run on a test basis to determine if i need to make more changes to the front-end (Access). The problem I am facing is how to keep the two databases in sync while I am testing. Any suggestions?

Also any suggestion or comments on how to run a test setup like this (in parrallel) are also welcome since this is my first time attempting a project like this.

Let me know if anyone needs more info.

Thanks in advance.

View 1 Replies View Related

Keeping The Audit Info Intact.

Apr 10, 2008

Hi all

I have a stored procedures with few DML stmts inside.

I need to log the success/failure of each DML statment into seperate audit table as follows

Begin
DML Stmt1
Log sucess/failure (Insert stmt to audit table)
DML Stm2
Log sucess/failure (Insert stmt to audit table)
end

when any of the above DML stmt fails, the entire transaction is getting rolled back including the audit information.

How can i keep the audit info intact when the transaction is rolled back.

Thanks.

View 1 Replies View Related

Keeping 5 Days Worth Of Backups

May 13, 2008

Hey all,

I'm trying to set up a backup schedule in SQL 2005 that will keep 5 .bak files within the folder I specify. I can get it to keep one backup only even though I specify that it is to keep 5 days worth of backups. Each backup happens at 12:00 am. I think the problem I may be having is I don't know how to get the maintenance plan to name the file mmddyyyy.bak or whatever it may need to determine that the file is 5 days old. Currently I am specifying a name of gmsqlbackup.bak. Any idea's that may help me out?

View 2 Replies View Related

Keeping Report Pages From Showing

Jun 18, 2008

I have a report that pulls a customer balance.
In crystal there was a way to have the page not show up if it met a certain criteria(say if the balance was 0 or negative).

I'd rather not filter them in SQL because it takes a few calculations to figure out what their balance is, and I already have SRS doing that calculation.
So is there a way to have a report page not print based on a certain criteria?

View 4 Replies View Related

Importing Tables Keeping Username

Feb 4, 2008

i'm importing tables from another sql server -
now the tables are coming is as
esther.tablename (my username on the other server)
instead of
dbo.tablename

how can i change this?

View 9 Replies View Related

Dev. Team - Keeping Three Tiers In Sync

Jul 23, 2005

This may not be a MSSQL-specific question, butI wanted to ask it here first, in case there'sa MSSQL and/or SourceSafe solution that will help.Our dev team is having some difficulty withkeeping the nightly builds in sync with thestored proc mods. I'm wondering if there aresome good case studies on how to avoid this"drift". Something like genning a new DB fromchecked-in SPs, etc. alongside each regular build,then always have a paired enterprise app/databaseduo that is tagged and added to a history.FWIW, we have a 3-tier .NET/C# app, andADO.NET is throwing exceptions every otherday.If the suggestion is to whip the DB guys, thatworks for me as well. ;-)Nah, there's much love there.Thanks in advance,~swooz

View 2 Replies View Related