Specifies the type of data being stored (string, date, numeric, etc).

learn more… | top users | synonyms

-1
votes
0answers
38 views

Equivalent data types in Oracle database for SQL Server 2008’s uniqueidentifier and timestamp

I am working on SQL Server 2008 R2, and I have data types which is specific for SQL Server, including: UniqueIdentifier Timestamp Now I want to deploy my asp.net mvc 5 web application on an ...
0
votes
0answers
28 views

Questions about the SET DATEFORMAT command [closed]

How do I set the dateformat for a column, where dates exist as '2014-12-28' and '2014-23-12' in the same column? I want it set as yyyy/mm/dd format.
0
votes
0answers
18 views

Best data type to store Active Directory Nullable GUID

I have a table name Employees,inside Sql Server 2008 r2. the table contain the following columns:- UserID. it is the Primary key, and it is auto generated by the DB. GUID. which will store the ...
0
votes
1answer
22 views

MySQL 5.6 Datetime Range

According to MySQL 5.6 doc, the supported range for datetime data type is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. But when I tried inserting values that are earlier than '1000-01-01 00:00:00', ...
1
vote
1answer
44 views

Standard data types for email address, telephone number , first name, rixh text editor, etc

I want to build a database for our system using Sql Server 2008 R2. but i need to know what are the best data types for storing these info:- Email address Telephone number with extension, such as ...
0
votes
2answers
56 views

Setting for default column type

Is there a setting in SQLServer 2008 and newer for default data type of date-time columns, i.e. datetime or datetime2? Actually more like something that would tell SQLServer to use datetime2 instead ...
0
votes
1answer
31 views

Blob datatype issue with log file

I am getting continuously below error message in mysqld.log [ERROR] InnoDB: The total blob data length (10152274) is greater than 10% of the redo log file size (3072). Please increase ...
1
vote
0answers
15 views

How to create a “match result” data type?

I'm building a football match table which contains one column "match result", which should consist of data like "3-1","0-0"... I'm wondering what kind of data type is best appropriate for it, varchar ...
1
vote
1answer
40 views

PostgeSQL 9.3: What is the difference between text and pg_catalog.text?

I have a classic 3-environments setting (dev, staging and production), each with its own RDS PostgreSQL 9.3. The client/server versions are psql (9.3.5, server 9.3.3). In order to keep the schemas ...
1
vote
1answer
82 views

Storing more than 8000 bytes in a column [closed]

I have a column in one of the tables in my SQL Server database that can have more than 8000 characters. As the maximum allowed character length for a column in SQL Server is 8000, how can I store the ...
0
votes
1answer
46 views

In a banking organization, why would account balance be declared as varchar and not decimal?

I worked in two organizations where I found money related columns are defined as VARCHAR data type. I didn't get correct explanation from anyone. As the database team is totally isolated with our ...
0
votes
1answer
51 views

12 hour format time in SQL datatype

I can convert time datatype to 12 hour format using convert etc. But is there any option to save the time in 12 hour format in SQL tables using any settings or property or any other methods?
0
votes
5answers
96 views

Database with only text fields

I started working on an existing SQL Server Database System in which most of the fields are stored as text. Except some IDs all fields are varchar (phone numbers, zip codes, Dates, addresses, monetary ...
0
votes
1answer
34 views

How to use timestamp/rowversion data types in Visio?

I am using Visio 2010 to create a ER diagram. When I insert the column names, I can't find timestamp or rowversion data types. Is there an alternative?
0
votes
1answer
39 views

Best datatype to store betting odds? [closed]

I have a lot of soccer betting odds (hundreds per match and thousands of matches), for example: <_10Bet_Home_Home>8.00</_10Bet_Home_Home> ...
3
votes
2answers
64 views

How to save an interval of years in postgresql

I have to save an interval of years, like: id intervalYears 1 2014/2015 2 2015/2016 3 2016/2017 And so on. Those years are like accademic years, so the first year must be lesser then the second, ...
1
vote
1answer
32 views

Is there a data type I can use for RA & Dec in astronomy

I need to have fields in my database for the following: RA (hh:mm:ss.00) Dec (Degrees:arcseconds:arcminutes.00) I need to have the facility to search for a range within each field. Any ideas?
1
vote
0answers
58 views

Best practice for storing image order number in a database

I am designing a social networking website(mysql with InnoDB engine + php) where I need to store image sequence in a database. The table structure is as follows:- PicId (BIGINT, AUTO INCREMENT, PK) ...
0
votes
1answer
72 views

Why does mysql give different result for this summations? [duplicate]

Can someone help understand the following behavior? mysql> select (1/3 + 1/3 + 1/3); +-------------------+ | (1/3 + 1/3 + 1/3) | +-------------------+ | 1.0000 | +-------------------+ ...
2
votes
1answer
84 views

PostgreSQL Division In Query Not Working

So I am having a really off day or something, but for the life of me I can not figure this out. I am trying to divide tier1 by total to get a percentage of the total. I thought like this: (tier1 / ...
2
votes
3answers
873 views

Query to return output column names and data types of a query, table or view

Is there a PostgreSQL query or command that returns the field names and field types of a query, table or view? E.g., a solution if applied to simple SELECT query like SELECT * from person should ...
2
votes
3answers
137 views

Constrain a postgres timestamp to “End of day”?

Given a table named bananas and a timestamp without time zone column named end_time and some psql clients have set timezone to 'UTC' and other psql clients have set timezone to 'US/Eastern' and the ...
-1
votes
1answer
168 views

Multiple conditions in a derived column ssis

Hi I am trying fairly new to SSIS , and my task is to convert dates in string as given below to a datetime. These are my date of birth in the source file (string data type): Source date of birth ...
0
votes
3answers
776 views

Convert yymmdd to datetime using ssis derived column

Below are the strings representing date of birth (yymmdd). I want to convert it into datetime format and load into a database using ssis. How can I do it? In my derived column I have ...
2
votes
2answers
93 views

Database events that will cause deletion of index

In SQL server if we try to change a data type of a column having index ,then it deletes the indexes associated with that column and changes the data type . I was not aware of it and was faced problem ...
1
vote
1answer
48 views

how to update the value of a period contained inside a bigger range

I have a tsrange like '[2010-01-01 14:00, 2010-01-01 15:00]',20 And want to update the 20 value to 15 in a range inside that, like '[2010-01-01 14:10, 2010-01-01 14:30]',15 My expected outcome ...
1
vote
5answers
234 views

What is the advantage of partition Date field into individual Year(int), Month(int), Day(int), Time(time) columns? [closed]

What is the advantage of partition Date field into individual Year(int), Month(int), Day(int), Time(time) columns? My manager said this is well known practice and query performance is good. Is this ...
3
votes
1answer
36 views

Why does the behavior of array syntax differ from '(?,?)' syntax when updating a point field and that field is NULL?

I'm using PostgreSQL 9.3.5. Suppose I have the following table: CREATE TEMPORARY TABLE point_test ("name" varchar(255), "pt" point); I then insert a row, leaving pt NULL: INSERT INTO point_test ...
1
vote
0answers
148 views

MS Access 365 When selecting Lookup Wizard, datatype is changed to Number

I want to create a relationship between my Contacts table & Ref_State table. Contacts table has field called State/Region which I want to make Data Type = Lookup Wizard Ref_State has fields ID ...
2
votes
2answers
381 views

Changing data type from mediumint to int without downtime

My current table is using MEDIUMINT for ID column ‘primary key auto increment’. The current table records soon will reach the maximum value for mediumint which is 8388607. That being the case, I need ...
5
votes
1answer
120 views

For alignment optimized table is bigger than original table - why?

In another question I learned that I should optimize the layout from one of my tables to save space and have better performance. I did this, but ended up with a larger table than before and ...
3
votes
1answer
114 views

Good layout of 3d point data for spatial queries in Postgres?

Like shown in another question, I deal with a lot (>10,000,000) entries of points in a 3D space. These points are defined like this: CREATE TYPE float3d AS ( x real, y real, z real); If I am ...
3
votes
1answer
22 views

Is 'internallength' always useful in custom type definition in Postgres?

In our database we use a custom type to store 3D locations: CREATE TYPE float3d AS ( x real, y real, z real); In many examples I see INTERNALLENGTH used as a parameter with the definition of ...
0
votes
1answer
119 views

Inheritance discriminator column better int or varchar or enum?

What is the best practice for storing a discriminator column ? Does it make any difference ? Any pros/cons of using one over the other ? I am using MySql with Entity Framework but the question goes ...
1
vote
1answer
21 views

Datatype for different categories in a database

I have got the following categories for accounting and cash flow purposes: Equity Contribution Distribution Valuation Debt Issuance Payment Valuation Allowance Interest ...
0
votes
1answer
32 views

What kind of data type to use for numbering in the Harmonized System?

The harmonized system uses a numbering that shows the hierarchy between different items. Similar to bookkeeping ledger numbers. Here is an example: 01 LIVE ANIMALS 0103 swine, live 010310 Purebred ...
1
vote
1answer
530 views

What datatypes can cause “String or binary data would be truncated”?

What kind of datatypes can cause the "String or binary data would be truncated" error? Varchars and other strings are obvious, but what about the "binary data"? Can this include integers or datetimes? ...
0
votes
5answers
145 views

define rating column in table?

Hi I am creating database for rating the items in my list but I want to ask what this if I have five stars rating how can I do it in database "rating" column should I use Int with check constraint or ...
4
votes
1answer
502 views

How do I decompose ctid into page and row numbers?

Each row in a table has a system column ctid of type tid that represents the physical location of the row: begin; create table t(id serial); insert into t default values; insert into t default ...
0
votes
0answers
245 views

Suppressing the removal of trailing zeros in numeric data type in sql server

I initially declared the data type of the columns for some of the tables i am working on as "NUMERIC". cause of that, trailing zeros are being removed. Is there anyway to suppress this behavior? i ...
1
vote
1answer
138 views

Alternatives to NULL for missing data [duplicate]

I am using PostgreSQL and trying to write value constraints. Is it possible to record missing data as distinct from NULL values in a numeric field without using an arbitrary numeric value? For many ...
0
votes
0answers
27 views

Hints about Datatypes

I'm currently writing some style guides for a new project where we want to use Oracle for the first time in a extensive manor. I already wrote down some naming conventions for tables, columns, pk, ... ...
4
votes
1answer
191 views

Do SQL Server Views update their data types automatically based on what tables feed them?

Someone mistakenly created a bunch of nchar fields in database tables. I noticed this, and after examining the issue, we're moving these fields to nvarchar types and trimming the values inside. In my ...
1
vote
1answer
150 views

Spliting Datetime into 2 Columns for Restaurant Reservation Schema?

Using PostgreSQL. The primary driver of this is that while no reservation will exist without a date and time, a user can search for and monitor reservations using a time or date or both. With a ...
1
vote
1answer
122 views

Function quote_nullable(timestamp without time zone) is not unique

When I run this query on "enterprisedb 9.2", it raises an error: select quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::date; Error: LINE 1: select quote_nullable(to_date('09-02-2014 ...
3
votes
1answer
107 views

Use of integer instead of interval (of one type)

Our DB design presently has a interval column which will only be storing days (no other interval type) so it is making sense to use INT2 (smallint) instead of interval. Reference to documentation. ...
0
votes
1answer
124 views

Why does Oracle store time in DATE column?

I have a table with a date column. I used JDBC to save a java.util.Date object into the table. When I run following query I get date aswell as a time value: select To_CHAR(departure_date,'DD-MM-YYYY ...
0
votes
1answer
69 views

Initializing values to a custom data type

I have defined a custom data type which I need to use in my procedure. Give below is my data type. CREATE or REPLACE TYPE CUSTOM_DATATYPE AS object( chkinvpre VARCHAR2(100), orderid ...
4
votes
2answers
186 views

How can I enforce ensure uniqueness of an association with a time dimension?

I have a database table AB which tracks the association of A and B over time: create table AB columns ( ARef ..., -- FK of A BRef ..., -- FK of B Since as Timestamp, Until as Timestamp, ...
0
votes
1answer
6k views

Float datatype with 2 digits after decimal point

In SQL server float datatype does not display leading zeros after decimal point declare @num as float set @num=5.20 select @num will dispaly only 5.2 But i want it to display as 5.20 After a ...