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 ...
