FAQ Database Discussion Community


Converting varchar(50) to datetime data type in SQL Server

sql-server,sql-server-2008,sqldatatypes
I'm trying to update varchar(50) field to datetime in SQL Server, tried couple of ways but no success. I ways get error Conversion failed when converting date and/or time from character string. Here is sample values in the OrderDateTime column: 2014-05-21 04:21:45.3346898 2014-02-26 08:43:58.5693805 2014-04-29 15:57:21.0778236 2014-04-09 07:23:57.3886545 2014-03-14 16:03:46.3919145...

Storing colours in a database

mysql,sql,colors,sqldatatypes
I've read this question and the accepted answer advises storing colour values as strings in a database. However, the purpose of storing those values for that question was for display purposes. I would imagine that using SUBSTRING() and CONV() to get the values and convert them to integers is slower...

Concatenating numbers in virtual column expression throws ORA-12899: value too large for column

oracle,numbers,sqldatatypes,oracle12c,virtual-column
While I gave this answer to a question yesterday, I suggested to use a VIRTUAL COLUMN for computed values instead of manually updating it. I did a test myself, and figured out an issue with the data size that the virtual column expression takes while concatenating two NUMBER type columns....

NUMERIC and VARCHAR

sql-server,sqldatatypes
I am using SQL Server 2008 R2 to run queries and I have come across a database where it stores numeric values as varchar(4). For example: SELECT [num] FROM [TABLE1] WHERE num > '95' I get the below results 96 97 98 99 999 However when I run the same...

SQLite: How to reduce byte size of integer values?

sqlite,optimization,types,integer,sqldatatypes
I have a SQLite table (without row ID, but that's probably irrelevant, and without any indexes) where my rows contain the following data: 2 real values, one of which is the primary key 3 integers < 100 1 more field for integers, but currently always null According to http://www.sqlite.org/datatype3.html, integer...

Creating Table in Oracle, SQL

sql,oracle,oracle11g,sqldatatypes,create-table
ORA-00907: missing right parenthesis I can't figure out what is going wrong with my dataypes and/or commas. Any help would be awesome. Thank you! CREATE TABLE RIDERS (RIDER_ID VARCHAR2(4) CONSTRAINT RIDERS_RIDER_ID_PK PRIMARY KEY, FIRSTRIDER VARCHAR2(20) CONSTRAINT RIDERS_FIRSTRIDER_NN NOT NULL, LASTRIDER VARCHAR2(20) CONSTRAINT RIDERS_LASTRIDER_NN NOT NULL, ADDRESS VARCHAR2(25), CITY VARCHAR2(15), STATE...

More precision needed from SQL Server Money data type

sql,sql-server,asp-classic,sqldatatypes,money
I am working on this old SQL Server database which store numeric values in MONEY datatype. This has been good for years, now for some currency rate conversions we need up to 10 decimal places. We are exploring the possible conversion from MONEY datatype to DECIMAL. I see that a...

SQL Server 2008 Nvarchar(Max) concatenation - Truncation issue

sql-server,tsql,sqldatatypes,nvarchar,truncation
Can someone please explain why this is happening on SQL Server 2008: declare @sql Nvarchar(max); set @sql =N''; select @sql = @sql +replicate('a',4000) + replicate('b', 6000); select len(@sql) Returns: 8000 Multiple sites suggest that as long as first variable is of type NVARCHAR(MAX), truncation should not occur, but it still...

Is “NUMBER” and “NUMBER(*,0)” the same in Oracle?

sql,oracle,types,oracle11g,sqldatatypes
In Oracle documentation it is mentioned that NUMBER (precision, scale) If a precision is not specified, the column stores values as given. If no scale is specified, the scale is zero. But NUMBER (without precision and scale) is also accepting floating point numbers (34.30) but according to documentation if scale...

SQL data type time

sql,sql-server,time,insert,sqldatatypes
Is it possible to create a data type in SQL similar to time that only displays the hour and minutes instead of the hour, minutes and seconds. For example, If I inserted values into a table with the data type time I would get hh:mm:ss. How could I insert a...

Using VARCHAR in MySQL for everything! (on small or micro sites)

php,mysql,data,types,sqldatatypes
I tried searching for this as I felt it would be a commonly asked beginner's question, but I could only find things that nearly answered it. We have a small PHP app that is at most used by 5 people (total, ever) and maybe 2 simultaneously, so scalability isn't a...

What is the meaning of different integer mysql types

mysql,sqldatatypes
What is the difference between, for example, TINYINT and INT(1) or between TINYINT(3) and MEDIUMINT?

Nested tables - ORA-00902: invalid datatype

sql,oracle,oracle-sqldeveloper,sqldatatypes,sqlexception
I have a problem when I want to identify data type for my variable "price" as object type "rsd". When I create the table it retuns SQL Exception: ORA-00902: invalid datatype CREATE OR REPLACE TYPE "RSD" AS OBJECT ( currency number(9,2), MEMBER FUNCTION get_currency RETURN number) INSTANTIABLE NOT FINAL; CREATE...

MySql and float or double crop my number when I update it per query from php

php,mysql,database,floating-accuracy,sqldatatypes
I like to store Latitudes and Longitudes in a very precise way into my MySql Database with InnoDB. However, float did not offer enough internal decimal places so I switched to double. Wondering myself a little but MySql accepted double with a size up to 30 so I used double(30,27)...

UNION ALL two SELECTs with different column types - expected behaviour?

sql,standards,sqldatatypes,union-all
What is the expected behaviour due to SQL Standard when we perform UNION on two tables with different data types: create table "tab1" ("c1" varchar(max)); create table "tab2" ("c3" integer); insert into tab1 values(N'asd'), (N'qweqwe'); insert into tab2 values(123), (345); select c_newname as myname from ( select "c1" as c_newname...

Get record within 10km by lat lng in mysql

php,mysql,gps,sqldatatypes
Sorry for not handled the GPS data before. may be a newbie question So , I have created the table that store the gps data like this: table name : request id lat decimal(10,8) lng decimal(11,8) Recently, I would like to get the data that is 10km within the given...

SQL Server 2012 How to change the data type of a column from bit to datefield?

datetime,sql-server-2012,bit,data-type-conversion,sqldatatypes
I have a table Person with a column called onvacation. This column is of data type bit since it's a boolean in the code. It has values null, 0 and 1. I would like to change the data type of this column from bit to datetime so that all values...

Get table names from database, which contains perticular data type column

sql,database,table,sqldatatypes
I want to get all the Table Names from database which Table has a Column with particular DataType. Let's say image is the DataType, then I want to find all the Table Names which have one or more column of image type in it....

Structure types in T-SQL

sql-server,tsql,sqldatatypes
As I know SQL Server doesn't support structure types that contains another types, for example you can't make something like this create type User as(UserId int, Name varchar(10), Address varchar(255)) and then use it: declare @user User set @user.UserId = 10 ... Maybe someone knows any library/framework that helps to...