Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
The results are in! See what nearly 90,000 developers picked as their most loved, dreaded, and desired coding languages and more in the 2019 Developer Survey.

Questions tagged [type-conversion]

Questions about how to convert one data-type into another.

2
votes
1answer
43 views

Implicit Conversion of VARCHAR Column to NVARCHAR does not cause expected table scan

Given the following script, I can see implicit conversion and Data type precendence have a negative impact on a query plan -- create objects CREATE DATABASE ConvertTest GO USE ConvertTest GO CREATE ...
0
votes
0answers
27 views

Why am I suddenly getting type warnings on new database? [duplicate]

I restored a backup database from a 2008r2 server to a brand new instance of sql server 2017. When I run the same stored procedure, I am now getting type conversion warnings in the execution plan that ...
8
votes
2answers
176 views

Why am I getting an implicit conversion of Int / Smallint to Varchar, and is it really impacting Cardinality Estimates?

I'm trying to trouble shoot a slow performing query using Show Plan Analysis (SSMS) on the actual execution plan. The Analysis tool points out that estimates for number of rows are off from returned ...
4
votes
1answer
257 views

“ERROR: timestamp out of range” converting epoch stored as bigint to a timestamp

I'm getting ERROR: timestamp out of range: "1.52701e+15" when trying to convert an epoch stored as a bigint into a timestamp (value is taken from a real database table): select to_timestamp(...
1
vote
0answers
65 views

Converting an XML field to nvarchar causes warning on main SELECT in explain plan

I'm getting a warning on my main SELECT on one field in the explain/execution plan of my query on the following: SELECT ISNULL(CAST(p.loadDiagnostic as nvarchar(4000)),'''') FROM myTable p This ...
0
votes
1answer
34 views

Arithmetic overflow for Variable in Openrowset

I try to create a table to store the used storage Information for every Database. Since theese are hosted on different Cluster / Instances, I created a table with all Instance / Server Information. ...
2
votes
1answer
45 views

Converting a string to an object reference

Sometimes I end up in a position where I have a string version of my object reference like so: 'server.database.schema.table' And I need to use it as an actual object reference: 'server.database....
2
votes
3answers
978 views

Convert Varchar To Datetime and Add Seconds

I have the following two columns in my table callstarttime callduration 20180101215910 120 20180101215710 220 The first column's data type is varchar(255) whilst the second ...
0
votes
1answer
178 views

Equivalent of DB2 function “decimal(numeric expression, precision, scale)” in PostgreSQL

Is there an equivalent of the DB2 function decimal(numeric expression, precision, scale) in PostgreSQL? Or how would I do this functionality in PostgreSQL? The DECIMAL function returns a decimal ...
-3
votes
2answers
1k views

Conversion failed when converting the varchar value to data type int [closed]

declare @targetnumber int declare @numberfield varchar(20) = 'stepnum' declare @sourcetable varchar(20) = 'tblsteps' declare @idfield varchar(20) = 'stepid' declare @recid int = 3 set @...
-2
votes
1answer
782 views

Varchar Overflowed an Int Column [closed]

I am having an error with the following query: SELECT scp.phone, scp.extension, scf.fax, sce.email FROM supplier_contacts AS sc LEFT OUTER JOIN supplier_contact_phone AS scp ON sc....
0
votes
3answers
4k views

mongodb: update string field to integer

I have a collection that has some fields that should be integers. Due to a software bug, some of these values are strings. I need to convert them. Here is an example document: var coll = db....
1
vote
1answer
1k views

Assigning empty string to INT NULL field in MySQL

Point me a reference what happens if empty string ('') is assigned to a INT NULL field. Does it become NULL or 0? Do all versions (starting from 5) of MySQL do the same?
4
votes
1answer
175 views

How do I list available casts in SQL Server?

In PostgreSQL, there is a command, \dC+ I can use in psql \dC+, \dC[+] [PATTERN] list casts To lists the possible CASTS. This is useful, because I can easily find out what I can use to cast to ...
1
vote
2answers
134 views

PostgreSQL truncating values of real numeric type

Using PostgreSQL 9.2 on Windows Server 2007, I have the following table: CREATE TABLE aaa_dens (read_no INT, position REAL PRIMARY KEY); Some values in position have 4 or more digits after the ...
1
vote
0answers
84 views

bit columns generating warnings - how can a cast in the field list affect the cardinality estimate?

A few days ago I had a question about warnings in the execution plan Type conversion in expression may affect “CardinalityEstimate” - on a computed column? and that was solved, thanks Paul. Now I ...
0
votes
0answers
81 views

How can I convert integer value from query_hash_s in Azure Log Analytics to a VARBINARY(64) for sys.dm_exec_sql_text?

SQL Server errors are shown in Azure Log Analytics. Sometimes, the query_hash_s column is populated. But the values provided appear to be BIGINT. How can I use that value to find the actual text of ...
1
vote
1answer
54 views

Is there any slowdown in casting from varchar to text?

In writing this answer, I'm just wondering if there is any slow down in casting from varchar to text or if that cast is just a compile-time trick. I've been told they're represented the same way and ...
3
votes
1answer
95 views

Implicit conversion causing error part of the time

I have been scratching my head about this one for the last day or so - I just can't see why a procedure works in one environment, but then fails in another due to a conversion error (same data, same ...
-3
votes
1answer
722 views

SQL Server - Force INSERT statement to use varchar instead of int [closed]

I'm trying to make a large insert to a new table in SQL Server that's set up with a particular column of type varchar(56). Most of the values for that column in the insert rows are integer values, but ...
1
vote
1answer
115 views

What is the equivalent of the SQL Server CHAR function?

SQL Server has CHAR ( integer_expression ), so I can do SELECT CHAR(65) What is the equivalent on Postgresql? I have tried SELECT E'\0x65'; but I get ERROR: invalid byte sequence for encoding "...
0
votes
1answer
230 views

how to set timestamp in datetime field if the time is not specified?

I have a Stored Procedure having input parameter as @startDate as datetime and I am using this parameter to compare this fields to table column. I want to convert that @startDate field if the ...
4
votes
2answers
2k views

How to avoid implicit type casts in PostgreSQL?

I just discovered that I can insert values of any type into a PostgreSQL (9.6) column of type text: drop table if exists d cascade; create table d ( a text ); insert into d values ( 42 ); insert into ...
0
votes
1answer
455 views

Converting a date to a datetime in MySQL

I'm supposed to add time to a date field. We use MySQL with python3, alembic and sqlalchemy. Now I've written a conversion program. It is my first program of such a kind. I'm not certain how it will ...
-3
votes
1answer
112 views

Why can't a function that accepts an int, be overloaded to accept an interval?

Currently getting an error about type casts, HINT: Could not choose a best candidate function. You might need to add explicit type casts. Let's say I create an overloaded function foo CREATE ...
3
votes
2answers
200 views

How SQL Server knows when to implicitly convert the values

Following my previous question: Implicit conversion does not affect performance I used below simple query select count(*) from fpc where SKey in ('201701', '201702A') SKey is of type int and I have ...
4
votes
2answers
104 views

Implicit conversion in fetch

While studying different query plans to improve performance I noticed that FETCH is doing an implicit convert to bigint. Example table and query: CREATE TABLE checkPagintion ( Id INT NOT NULL ...
4
votes
1answer
2k views

what are the possible reasons for 'Arithmetic overflow error converting expression to data type int' in SQL server

When this command is run, there is SQL server error 'Arithmetic overflow error converting expression to data type int'. SELECT sum(size) FROM [dbname]..sysfiles sf, [dbname]..sysfilegroups sfg ...
6
votes
7answers
1k views

Why does SQL try to convert rows that are excluded by my where clause?

I've come across this multiple times, and I'm sure there is a good reason for it - but how do I avoid it? I'm sure it has to do with the quirks around isnumeric. In English, I have a view that ...
-2
votes
2answers
378 views

Update nvarchar value with GETDATE results

I would like to be able to update an nvarchar column with GETDATE(). e.g. if the value is currently 'ordered', I need to append the date. I did try : update order_line set comments = 'ordered'+ ...
0
votes
0answers
87 views

SQL Server 2014 datetime conversion

I have a date formatted as follows: 00:50:34 Jun 25, 2017 PDT .. actually I have it in this format 00%3A50%3A34+Jun+25%2C+2017+PDT but can easily transform in the above. I was wondering if there'...
7
votes
1answer
245 views

Conversion error on paramater but not variable

I've got a stored procedure that accepts a parameter; CREATE PROCEDURE [dbo].[Stored_Proc_Name] (@ParamID NVARCHAR(255) = NULL) I call this stored procedure and pass in a uniqueidentifier, something ...
0
votes
0answers
15 views

Going JavaScript to PostgreSQL and back! [duplicate]

In JavaScript we work with nested/complex objects. In PostgreSQL we have composite types and JSONB. However, converting between JS types and PG types is not straightforward. I am currently using ...
0
votes
2answers
380 views

Converting spatial data from Oracle to MySQL

This is a followup to this question: Problem getting zips in raius via MySQL I have some spatial stuff in an Oracle table. MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),...
-5
votes
1answer
2k views

“Conversion failed when converting date and/or time from character string” [closed]

Create Table EMP ( EMP_NUM Int, EMP_FNAME Varchar(20), EMP_LNAME Varchar(20), JOB_CLASS Varchar(10), HIREDATE Date )
0
votes
1answer
494 views

How to handle the junk characters upon conversion of the data from image datatype to varchar?

We are trying to convert the column of type image to column of type varchar(max). We tried the below way to do the conversion. --col1 is of type image --col2 is of type varchar(max) set col2 = ...
0
votes
1answer
260 views

Query fails from application but runs fine in SSMS

I have a query that runs fine when executed from SSMS but when done so from the application, it fails with a (database level) exception. select ksprintid from kshistory_akt h where cast(h.serialno as ...
2
votes
2answers
2k views

How to know which record caused an error

I have a table named TEMP1 with 5 columns that are all NVARCHAR(100) I want to transfer the contents of TEMP1 to a table named FINAL, which has the same columns, but this time with targeted data ...
5
votes
1answer
427 views

SSIS changes SQL Server source data type from date to WSTR

I want to work with a table from SQL Server in an SSIS data flow. One of the columns of the table is date type. When I import it to SSIS using OLE DB source component, SSIS changes the metadata of ...
1
vote
0answers
54 views

Are there any pitfalls to be aware of when using dynamic type-coercion in SQL Server?

I want to insert into a database with a query that looks something like the following: INSERT INTO <table name> (<set of column names>) VALUES (<set of corresponding values>); As I ...
1
vote
0answers
163 views

How to explicitly convert to fixed-length alphanumeric datatype in 4D

I'm building an ETL process in SSIS that extracts data from a 4D data source using the manufacturer's own ODBC provider (4D v16 ODBC Driver). When I try to extract any type of "string" column, it ...
1
vote
0answers
44 views

DateTime to Format: Implicit Conversion [duplicate]

I have a datetime I want to convert to a formatted date and time. The format is 109(9). I have always used: CONVERT(char(26), c1.DateTime, 109) Which works successfully, but at least on SQL Server ...
0
votes
1answer
88 views

Fast Conversion to Big INT

I have a huge number of large databases (300+!) that vary in size from teeny tiny to absolutely massive (120 GB+) in size. I've already determined a way to generate scripts to decide what indexes, ...
0
votes
2answers
2k views

the result of REPLACE and CONVERT is a VARCHAR(8000) - how can I have a VARCHAR(50) as a result instead?

I have many situation where I need to convert big float numbers with many decimal places to a rounded number with 2, or 1 or no decimal places at all. the way I have been doing this is demonstrated ...
-1
votes
1answer
45 views

How to know a downloaded database file is secure?

maybe it's two questions but maybe someone has already faced with my issue exactly, and just knows ONE precise answer during this conversion target described below. I have downloaded an empty .mdb ...
4
votes
2answers
227 views

SQL 2005 table data type “text” to nvarchar

I have this column Remark which is data type text. The longest length is 153. So is it safe to say that if I change this column from text to nvarchar(200), I won't lose any data? SELECT LEN(LTRIM(...
0
votes
1answer
316 views

How do I go from a PostgreSQL native point type to a PostGIS point?

If I have a point SELECT point(long,lat); How do I go from that to a PostGIS point?
1
vote
1answer
339 views

Convert rfc822 to either a GMT DATETIME or Unix timestamp in SQL Server

I am trying to find a way to convert an rfc822 string date (with timezone) to either a GMT value of data type DATETIME or a unix timestamp of data type INT. For example: Mon, 15 Aug 2016 11:36:36 UTC ...
0
votes
1answer
485 views

Convert timestamp type during INSERT

I have a table of raw data as timestamp with timezone, as well as other extraneous columns. I want to convert each timestamp to the interval difference between it and the earliest (smallest) timestamp ...
4
votes
2answers
3k views

How to avoid implicit conversion for an Integer column

SELECT [BusinessEntityID],[NationalIDNumber],[LoginID],[OrganizationNode] FROM [AdventureWorks2014].[HumanResources].[Employee] where BusinessEntityID = 5 I get convert_implicit() function called ...