Data Types list

Hi all,

Im trying to find a list of the different datatypes used when importing a text file,

Such as float [DT_R4], Currency [DT_CY], four-byte signed integer [DT_14] etc etc

I would like to use the list to accurately match up datatypes on paper before I build a package so I can also double check them with friends to see if they are the right one's to use

So I just wondering if anyone knew a place where they were stored Ive gone through the BOL but to no avail.

Small problem really but it would be very handy for me.

Thanks

I wrote out the list this morning, if anyone else ever needs

Boolean [DT_BOOL]
byte stream [DB_BYTES]
currency [DT_CY]
database date [DT_DBDATE]
database time [DT_DBTIME]
database timestamp [DT_DBTIMESTAMP]
date [DT_DATE]
decimal [DT_DECIMAL]
double-precision float [DT_R8]
eight-byte signed integer [DT_18]
eight-byte unsigned integer [DT_U18]
file timestamp [DT_FILETIME]
float [DT_R4]
four-byte signed integer [DT_I4]
four-byte unsigned integer [DT_UI4]
image [DT_IMAGE]
single-byte signed integer [DT_I1]
single-byte unsigned integer [DT_UI1]
string [DT_STR]
text stream [DT_TEXT]
two-byte signed integer [DT_I2]
two-byte unsigned integer [DT_UI2]
Unicode string [DT_WSTR]
unicode text string [DT_NTEXT]
unique identifier [DT_GUID]

GL all

data type to store time

Hi all ,

What datatype should I take to store time in a table -- datetime , float or decimal?

my requirement is to store "Worked Hours in a day by an employee" in the field say, 9 hrs and 30 mins.

I should be able to manipulate data in this field such as total hours present in the month, extra hours worked in a day (considering 9 hrs as standard time),less hours worked in a day, and so on

I would suggest the builtin DATETIME datatype. The advantage of this datatype is that it gives you the ability to directly use the builtin functions that come with TSQL.|||

As Kent indicated, using a datetime is most likely the best option. Yes, it will put the default date of Jan 1st, 1900, but you are interested in the hours.

You will be able to use datetime math functions, AND you don't have to remember to convert 20 minutes to .333333 hr, etc.

IF you are not concerned with seconds and milliseconds, you might consider a smalldatetime datatype.

|||Thanks Kent and Arnie for the quick response. I 'm new to this forum and sql server. Hope many more responses from you guys

data type question

Would float be the best choice for a data type using latitude and longitude values...i.e. " 33.606379" " -86.50249"
Thanks,
-D-The FLOAT datatype is a logical choice for representing Latitude and Longitude measurements, as long as your database sees them as "measurements" instead of exact values. If you need to treat them as counts instead of measures, you'll probably want to use NUMERIC instead of FLOAT.

-PatP|||Hi Dman100

I hope this is valuable to the thread and not a hijack. I am interested in appropriate use of approximate floating point numbers (well - a bit).

I have pretty well eliminated float and real datatypes from all my databases. This is because the majority of the applications I support and produce require absolute values. As far as I can tell - if the application of a numeric field requires some sort of mathematical manipulation (especially to a high degree of precision) and the number concerned is absolute then float and real are poor choices for the field data type. The exception would be a numbers that cannot be absolutely represented by a fixed number of digits (one third, pi etc).

I am not mathematically trained to the sort of standard many of the SQl gurus are. I am not certain the above is correct. I am happy to be corrected. I am happy to be told that the above is entirely irrelevent to the question in hand. I am happy to be told to bog off. I am, in fact, happy.|||It is good to be happy!

You hit the nail pretty much on the head, saying the same thing that I said in a bit more roundabout way.

Numbers can be looked at two different ways by most computer languages, and SQL can deal with them either way.

One way to think about a number is as a count... It is exact, repeatable, and can be "proved" in some way. The data types INT, BIGINT, DECIMAL, and NUMERIC are well suited for counts.

The other way to think about numbers is as a measurement... A measurement can be quite exact (to N decimal places), but it can't be "proved" like a count can. The data types REAL and FLOAT are well suited for measurements.

Values that can be derived from computation (such as speeds, accelerations, and many forms of location) are inherantly measurements. While they can be quite precise, there isn't a way to derive them from a count (other than to use one or more counts to mathematically derive the measurement). Speed as such is relative, so there isn't a direct way to count it... The best you can do is measure or count the distance traveled and the time used to compute the speed. While you might be able to count units of distance and time, there is no way to count units of speed (contrary to the belief of my neighbors in college).

The problem is that sometimes you need to deal with people that don't understand the difference between a count and a measurement. They think you should be able to store a measurement to N digits, and always have the exact same value come back. This isn't unreasonable from their perspective, and it makes perfect sense to them... They see nothing silly about the assertion that 3.141592654 is the value of pi, because to them that is a true statement.

I'm going to cut my blither short here... I've probably blabbered far more than anyone wanted to read already. The short answer boils down to REAL and FLOAT are for measurements. Most people prefer to think in counts, so most databases use INT or NUMERIC.

-PatP|||You hit the nail pretty much on the head, saying the same thing that I said in a bit more roundabout way.That pretty much sums up my entire career to date :D

Thanks Pat - you've firmed up my understanding.|||you guys are good

if i write a query likeselect x * 3.141592654 ...what datatype is that, DECIMAL or FLOAT?

and wouldn't it be better to use select x * ( select value from constants where name='pi' ) ...to allow you to define the value of pi in one spot, so that all queries could use it, so that, you know, in case the value ever changes, you wouldn't have a ton o' queries to change...|||you guys are good

if i write a query likeselect x * 3.141592654 ...what datatype is that, DECIMAL or FLOAT?

and wouldn't it be better to use select x * ( select value from constants where name='pi' ) ...to allow you to define the value of pi in one spot, so that all queries could use it, so that, you know, in case the value ever changes, you wouldn't have a ton o' queries to change...While I've heard that there is one state that has changed the value of pi to meet biblical requirements, I don't see how that would justify creating a table of constants to cope with that kind of problem. There are too many variables that I couldn't predict to make that practical. Even if we considered creating such a table as an option, it wouldn't help with the data type, only the value being used.

Its a good idea Rudy, and one that I wouldn't expect from you, but I just don't see it as practical in this particular case. ;)

-PatP|||oh my god, pat, can't you tell when someone is kidding

"in case the value of pi ever changes" -- you thought i was serious??

that's hilarious

:)|||oh my god, pat, can't you tell when someone is kiddingYou have to watch for those smilies... Sometimes they sneak in at the end!

It still wouldn't change the data type.

-PatP

Data type conversion problem

Hi,
I have a variety of fields that I am trying to convert from Float to Decimal
for greater accuracy however we are having some problems converting them.
We use this fantastic tool called "SQL compare" by Red-Gate software except
that it's failing in one spot. So, we are now working with the remaining
fields that need to be converted field-by-field.
When we try to convert the field called dpst_amt it fails with:
Msg 8115, level 16, State 6, line 1
Arithmetic overflow error converting float to data type numeric.
Statment terminated
The values in [dpst_amt] field are numbers like 693.399999999998, if I try
[select round(dpst_amt,2)] the value never is rounded, if I try to copy the
value out to X field it fails as well. How did I determine what is in these
fields, meaning how do I find the bad record? One must be bad but where or
how do I find it?
Thank you!
ChrisWhat is the size of your decimal datatype? There must be some data in your
column that is larger than the largest that fits in the decimal datatype, so
if your decimal is for example decimal(6,2)
SELECT dpst_amt
FROM your_table
WHERE ABS(dpst_amt) > 9999.99
should give you the values that are outside the range of your decimal.
SELECT MAX(ABS(dps_amt)) FROM your_table
will give you an indication as to what the size of your decimal datatype
should be.
Jacco Schalkwijk
SQL Server MVP
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:ueaB9$xWFHA.3876@.tk2msftngp13.phx.gbl...
> Hi,
> I have a variety of fields that I am trying to convert from Float to
> Decimal for greater accuracy however we are having some problems
> converting them. We use this fantastic tool called "SQL compare" by
> Red-Gate software except that it's failing in one spot. So, we are now
> working with the remaining fields that need to be converted
> field-by-field.
> When we try to convert the field called dpst_amt it fails with:
> Msg 8115, level 16, State 6, line 1
> Arithmetic overflow error converting float to data type numeric.
> Statment terminated
> The values in [dpst_amt] field are numbers like 693.399999999998, if I try
> [select round(dpst_amt,2)] the value never is rounded, if I try to copy
> the value out to X field it fails as well. How did I determine what is in
> these fields, meaning how do I find the bad record? One must be bad but
> where or how do I find it?
> Thank you!
> Chris
>|||Originally the datatype was Float and what I've found since writing this was
the value of [1.#INF] was stored in the field. How does one search for that
value in the future?
Thank you!
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:eukytqyWFHA.2912@.TK2MSFTNGP10.phx.gbl...
> What is the size of your decimal datatype? There must be some data in your
> column that is larger than the largest that fits in the decimal datatype,
> so if your decimal is for example decimal(6,2)
> SELECT dpst_amt
> FROM your_table
> WHERE ABS(dpst_amt) > 9999.99
> should give you the values that are outside the range of your decimal.
> SELECT MAX(ABS(dps_amt)) FROM your_table
> will give you an indication as to what the size of your decimal datatype
> should be.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:ueaB9$xWFHA.3876@.tk2msftngp13.phx.gbl...
>

Data type conversion

Does anyone knows which data type conversion is better to use for
numeric/double in Access?
When upsizing or using dts, sql server uses float with 8 precision. Should
it better be decimal with 2 digit left and 2 digits right the comma.
ThxIf float-8 does the job, then there's no reason not to use it. You should
format the output at runtime, not when you store it, so you're okay.
- Wm
--
William Morris
Product Development, Seritas LLC
Kansas City, Missouri
"Ezekiël" <ezekiel@.lycos.nl> wrote in message
news:%23jeq61D3DHA.1760@.TK2MSFTNGP10.phx.gbl...
> Does anyone knows which data type conversion is better to use for
> numeric/double in Access?
> When upsizing or using dts, sql server uses float with 8 precision. Should
> it better be decimal with 2 digit left and 2 digits right the comma.
> Thx
>

Data type conversion

Does anyone knows which data type conversion is better to use for
numeric/double in Access?
When upsizing or using dts, sql server uses float with 8 precision. Should
it better be decimal with 2 digit left and 2 digits right the comma.
ThxIf float-8 does the job, then there's no reason not to use it. You should
format the output at runtime, not when you store it, so you're okay.
- Wm
--
William Morris
Product Development, Seritas LLC
Kansas City, Missouri
"Ezekil" <ezekiel@.lycos.nl> wrote in message
news:%23jeq61D3DHA.1760@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Does anyone knows which data type conversion is better to use for
> numeric/double in Access?
> When upsizing or using dts, sql server uses float with 8 precision. Should
> it better be decimal with 2 digit left and 2 digits right the comma.
> Thx
>

---