Questions tagged [type-conversion]
For questions about converting one data type into another, or issues arising from that process.
123
questions
2
votes
1answer
30 views
How to combine date and time with a string value in column created by Case in SQL Query
I am in the process of creating a query for an SSRS report that will show a column that has DATETIME values. The column can have NULL values. In my query I would like to use a CASE expression.
I ...
2
votes
2answers
138 views
Implicit conversion between decimals with different precisions
I know that when SQL Server handles an implicit conversion between types it converts the lowest priority one to the highest one.
But what is the result datatype when I perform an operation between ...
2
votes
1answer
408 views
How can I make this execution plan more efficient?
I have worked out all the implicit conversions, but I still see mentions of it in the plan. I have attached the plan, and any recommendation will help.
select cardholder_index, sum(value) as [RxCost] ...
6
votes
1answer
379 views
SQL Server error 242 with ANSI datetime
I'm trying to debug a MS SQL Server error 242:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The error is originated by this statement:
...
4
votes
1answer
123 views
No IMPLICIT_CONVERSION warning in this query plan
Take the following minimally complete and verifiable example1 code:
USE tempdb;
DROP TABLE IF EXISTS dbo.t;
GO
CREATE TABLE dbo.t
(
t_id int NOT NULL
CONSTRAINT t_pk
PRIMARY KEY
...
3
votes
2answers
114 views
Index Seek scanning whole table dependent on parameter value
I have a query:
SELECT Id,
ColumnA,
ColumnB
FROM MyTable
WHERE ColumnA = @varA OR
ColumnB = @varB
The table is defined as
CREATE TABLE MyTable
(
Id INT IDENTITY(...
0
votes
1answer
172 views
How to convert date and/or time from character string as variable field
I have a backup report which looks to see if all databases have been backed up within the last 30 days, and want to insert 'Not all databases backed up in last 30 days' into that field (datetime type) ...
5
votes
2answers
244 views
How to get SEEK accessing converted ID via view
Assume I have a table:
-- just for test purposes
CREATE TABLE SomeTable (
ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK__SomeTable__ID PRIMARY KEY CLUSTERED
,SomeColumn1 NVARCHAR(50) NULL
,...
2
votes
1answer
98 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 ...
9
votes
2answers
212 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
818 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
75 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
42 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
49 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
1k 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 ...
2
votes
4answers
6k views
Converting columns from NVARCHAR to VARCHAR
I have several large tables with many NVARCHAR(MAX) columns. This database is only for US use and I don't think there are any foreign characters or letters in any of the columns, so I wanted to look ...
0
votes
1answer
623 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 ...
-2
votes
2answers
8k 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
1k 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
7k 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
2k 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
442 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
92 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
96 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
61 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
123 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 ...
-2
votes
1answer
1k 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
195 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
298 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
3k 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
572 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 ...
-2
votes
1answer
156 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
222 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
108 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
3k 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
2k 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
491 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
90 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
278 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
453 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),...
0
votes
1answer
572 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
358 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
4k 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
501 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
56 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
190 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
46 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 ...
