The tag has no usage guidance.

learn more… | top users | synonyms

11
votes
1answer
307 views

CASE expression returns wrong value when using CEILING

I've run into an issue where a CASE expression does not return what I expect. As a test, I added a decimal variable and ran the same CASE expression against it and it works fine, returning the ...
10
votes
1answer
7k views

Is there such thing as Custom Data Types?

Does MySQL have any support for custom data types? For example, zip codes might be stored in a varchar(10) field, but they could be compacted in to an int, with options for blank, and a flag as to ...
8
votes
2answers
6k views

Why does “SELECT POWER(10.0, 38.0);” throw an arithmetic overflow error?

I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns. As part of the check I compute the size of the data type's range for every IDENTITY column; I use ...
8
votes
1answer
2k views

Warning in query plan “Cardinality Estimate”

create table T(ID int identity primary key) insert into T default values insert into T default values go select cast(ID as varchar(10)) as ID from T where ID = 1 The query above has a warning in ...
7
votes
1answer
26k views

How can I convert from Double Precision to Bigint with PostgreSQL?

I need to convert a value of Double Precision to Bigint with PostgreSQL. How can I do that? I have tried with to_bigint(myvalue) but that function didn't exist.
6
votes
1answer
289 views

Converting CHAR to DATETIME so I can use DATEADD()

I have a question regarding using a DATEADD() function to a column that is identified as CHAR (6) The time_stamp column holds values like 131329 as hhmmss. Whenever I try to create a new column which ...
6
votes
1answer
79 views

Why does assigning FOR XML to an XML variable warn about CONVERT_IMPLICIT(XML)?

SET STATISTICS XML ON; DECLARE @x XML = (SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml')); Type conversion in expression (CONVERT_IMPLICIT(xml,[Expr1001],0)) may affect ...
5
votes
2answers
26k views

Convert a datetime column to number of seconds

In my SQL Server database, I have a datetime column. What is a good way to create a new column that represents the long value for the datetime column. The long would represent a number of seconds.
5
votes
4answers
54k views

Conversion of a varchar data type to a datetime data type resulted in an out-of-range value

I am trying to run a simple query to get all rows created in November: SELECT COUNT(*) FROM dbo.profile WHERE [Created] BETWEEN '2014-11-01 00:00:00.000' AND '2014-11-30 23:59:59.997'; SMSS ...
5
votes
2answers
37k views

Converting Non Unicode string to Unicode string SSIS

I am creating a package where I will be exporting data from a database into an empty excel file. When I added only the source and destination components and I ran the package I got a conversion error ...
5
votes
1answer
439 views

How can I make this query sargable?

I have a query which joins two tables based on a timestamp value (don't ask). One of these tables stores the data in a column with a Timestamp data type. The other stores it as a varchar. ...
5
votes
1answer
41 views

SQL Query not throwing error even if the entered scale value is higher than specified

I need to capture numeric values without truncation. But the output value is getting truncated even though I specified the Scale as '2'. Column 'A' datatype - numeric(5,2) Case 1: Entered value: ...
4
votes
3answers
491 views

Unknown returned type in PostgreSQL query

The following query works: SELECT a, b FROM unnest(ARRAY[(1,2), (3,4)]) AS t(a integer, b integer); a b _ _ 1 2 3 2 However, I wasn't able to use a different column type such as varchar(255): ...
4
votes
4answers
772 views

Changing the representation of NULLs in pg_dump plaintext output

I'm trying to convert a large-ish Postgres DB (500 GB) to SQL Server 2012. After investigating a few third-party tools and being disappointed in features, performance, or both, I started pursuing a ...
4
votes
1answer
2k views

Why does implicit conversion from sql_variant (basetype decimal) not work well with float

Why is ResultFloat = 0 in the query below? Am I missing a point within the cast/convert, sql_variant docs? declare @v sql_variant = convert(decimal(28,8), 20.0); select ...
4
votes
2answers
1k views

Change column data type across database

I have a bunch of tables with many columns of data type ntext. I wish to change all such columns in all tables of a particular database to nvarchar(max), because of planned deprecation (EDIT: also ...
4
votes
1answer
181 views

Exchange rate table and conversion on the fly in query

I'm working on a small project and I'm have arrived to the point where I let a user chose in which currency to display items. I'm working with Java and my first thought was that I would use some kind ...
4
votes
1answer
295 views

Type safety in WHERE clauses

I've spent the most part of yesterday tracking down a strange bug in our PHP/MySQL application and the culprit seems to be a poor query. We have a table like ...
3
votes
3answers
1k views

Converting CHAR to BIT

I have a column with char(16) data type and it was mistakenly set to char(16) actually it should be bit(1) because it contains only three distinct values as follows: 16 characters long string 10 ...
3
votes
2answers
4k views

Convert string numeric values with comma as decimal separator to NUMERIC(10, 2)

I have an SQL table of varchar colums which contain Greek formated numbers (. as thousand seperator and comma as decimal seperator) The classic conversion ...
3
votes
2answers
379 views

Convert int to RGB or hex color

I'm working with a database where colors are stored as integers. For some unknown reason, they are negative integers actually. Now, I need to use them in the CSS background-color property, which ...
3
votes
1answer
174 views

Conversions between any data type and the binary data types are not guaranteed to be the same between versions of SQL Server

I just found that on the SQL Server 2012 MSDN documentation for binary and varbinary types, they mention the following at the end of the article: Conversions between any data type and the binary ...
3
votes
1answer
6k views

Oracle: How to create a not null column in a view

Given a table like: CREATE TABLE "MyTable" ( "MyColumn" NUMBER NOT NULL ); I want to create a view like: CREATE VIEW "MyView" AS SELECT CAST("MyColumn" AS BINARY_DOUBLE) AS "MyColumn" FROM ...
3
votes
1answer
81 views

date type implicit conversion

When does oracle implicitly converts date type? on what parameters does it depend? I am running the following: SELECT SYSDATE - '01-01-01' FROM DUAL and got : SELECT SYSDATE - '01-01-01' FROM ...
3
votes
1answer
3k views

Type conversion with default fallback value

In PostgreSQL (8.4), I'm trying to convert a string parameter into a date within a SQL query, falling back to now() when the string is not a valid date (or empty). In "pseudo-SQL", this would be ...
3
votes
1answer
719 views

SQL Server to Oracle Export - Data conversion error

I have a rather weird problem with exporting a bunch of tables from SQL Server 2005 to Oracle database. I have SQL Agent job that deletes Oracle tables, recreates them and then exports the data using ...
2
votes
2answers
202 views

Convert hexadecimal to varchar

I have a column with values saved as hexadecimal. When I run this query: select column from table where column like '%22639935-KCN%'; This is the output I get: ...
2
votes
3answers
518 views

Help me to map Column types betwenn SQL and TopSpeed Clarion Tables

I am tasked to convert an old Clarion App to new one under SQL Server 2012. All I have is 200 tables with TPS extension which I can open only by program called TopScan. (No odbc, no technical ...
2
votes
1answer
40k views

Convert a date to yyyymmdd format

Which SQL command is recommended to convert a date to yyyymmdd format? convert(varchar(8), getdate(), 112); or convert(varchar, getdate(), 112) I notice that if I use the second one, it will ...
2
votes
2answers
215 views

How to convert unsigned int accidentally stored as signed int?

During a data conversion process, a field that was apparently an unsigned int was stored in a Sql Server int field(which is signed). To remedy this apparently requires some combination of bitwise ...
2
votes
1answer
3k views

Automatic conversion of anonymous records returned from an UDF to well known table-type

Proprietary code (that we cannot change) has a bunch of user defined functions of the type: create or replace function f() returns record as $$ ... $$ which we call in the following way (for ...
2
votes
2answers
2k views

Changing an nvarchar column to datetime

I am new to SQL Server and I have a problem. I created a database to store dates in an nvarchar field but now I need to convert that field to datetime. I searched the internet and couldn't find a ...
2
votes
1answer
78 views

How to convert _id field of all existing documents from type String to Number

At one of my collections I have an _id field that stores numbers. The field was defined originally in the Mongoose model as String. However, all the data concerning this field in the collection ...
2
votes
0answers
368 views

Same data type but getting error “Cannot convert between unicode and non-unicode string data types” [closed]

We have two SQL servers (source and destination). From what little information I've got, it seems to literally copy content from one server/database to another using a SSIS package (which I ...
1
vote
2answers
1k views

Convert datetime stored as char the YYYMMDDHHmmSS format to datetime

I'm working with a table where the date and time of an event is stored as a char in the format YYYMMDDHHmmSS or '20120606122012'. I need to compare this to the current timestamp to see if it's been ...
1
vote
3answers
3k views

SQL Server: CONVERT From ISO8601 To DateTime Within Query

I have a Microsoft SQL Server 2008 R2 database where the DateTime column is not a datetime data type, but varchar(50). Our website logs that data and I don't really have control over it (I'm sure a ...
1
vote
1answer
990 views

Query to convert the count to million or thousands

I am running a query on a database to return count(*) for number of rows. If the count is greater than million say 2,250,304, I want the query to return 2.25M. If the count is greater than 1000 but ...
1
vote
2answers
15 views

Sp_Conigure Keep value of xp_CMDShell, Show Advanced Options

I dont want to disturb the server values of xp_CMDShell & Show Advanced options. The criteria is to use SQL to run a batch file. This is the script i am trying to solve it with: DECLARE ...
1
vote
1answer
147 views

Extract time from date into new column

I have a varchar column named [Dato Time] which contains values like: 2011-05-12 00 2011-05-12 01 2011-05-12 02 So, a date and a Hour-specific time. I would like to split the date and the time up ...
1
vote
1answer
78 views

Subtract two dates stored as varchar

I have 2 varchars like: 20160316131824 20160316080002 How can I subtract to see if there is more than 24 hours of difference between the 2 values? I tried using the symbol - but it gives me an ...
1
vote
1answer
43 views

Report Configuration changes

I created a stored procedure that I got on Erin Stellato's course on Pluralsight to report if there are any configuration changes on the server as a nightly job as follows: CREATEPROCEDURE ...
1
vote
0answers
73 views

Improving the performance of procedures showing warnings for implicit conversions

We have recently migrated from SQL Server 2008 R2 to SQL Server 2012 Standard Edition. We and witnessing poor performance related to quite a number of stored procedures running against a database that ...
1
vote
1answer
74 views

How can I import latitude and longitude data into a database using Mysql Workbench?

I am currently trying to get some latitude and longitude data to import into a database using the data import wizard in mysql workbench. the problem is that the floating point values that get ...
1
vote
0answers
177 views

Does Postgres have a function for converting text into ASCII escaped or HTML escaped text?

Is there a builtin Postgres function that converts multi-line text into ASCII escaped or HTML escaped form and vice versa? I need to store multi-line text as name=value pairs, basically line .ini ...
1
vote
1answer
304 views

MySQLSyntaxErrorException on ALTER TABLE … CONVERT TO … in MySQL Connector/J

Running Ubuntu 14.04 with mysql-connector-java-5.1.28 installed. Let's create a table: CREATE TABLE mytable (value1 VARCHAR(32000), value2 TEXT, value3 VARCHAR(1000), value4 VARCHAR(32000)) ...
1
vote
0answers
53 views

What to do when the error message is longer than 4000 in T-SQL?

I have a OPENQUERY linking to an Oracle server that is failing. (I can't make any changes on the oracle server) But when it returns the error string it is cut short because it returns the query in ...
0
votes
3answers
93 views

how to convert this varchar to datetime format?

I have a varchar column has data like (2015-12-02 20:40:37.8130000) which i have imported from csv file to SQL table. i need to convert this column to datatime as (2013-03-17 14:15:49.687). ALTER ...
0
votes
3answers
5k 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 ...
0
votes
1answer
754 views

Importing data from MS Access database to an existing Postgres database

I'm developing a Django based website with a Postgres DB. The problem I'm having is importing the data from an Access DB into it. To be specific, I'm not trying to translate/import the schema from ...
0
votes
1answer
173 views

Mysql 5.5 to Mysql 5.6 upgrade Insert query is breaking

Assume column with the following definition product_id INT NULL NO_DEFAULT product_id_nn INT NOT NULL NO_DEFAULT and query insert into table (product_id,product_id_nn) values ...