Questions tagged [type-conversion]
For questions about converting one data type into another, or issues arising from that process.
132
questions
1
vote
1answer
61 views
Casting int YYYYMMDD to date produces incorrect value in Squirrel-SQL
I have extracted some data from a legacy database where dates are stored as Numeric(8) values, in YYYYMMDD format. I've stored them in a staging table in that same format, and now I would like to ...
0
votes
1answer
28 views
Why when I JOIN to a casted field in an index view, the execution plan warns about “type conversion in expression”?
I thought an indexed view was materialized and therefor stored on disk. If that's the case, I would've thought that any type conversions I'm doing in the view would materialize as that datatype on ...
2
votes
0answers
36 views
Postgres 11: fastest way to convert a small list of numbers to text (to be used as NOTIFY payload)
I am on Postgres 11.5 and in a trigger function I issue one or more pg_notify calls for each row modified in the transaction. The most common payload is a list of 12 float numbers, representing two ...
1
vote
0answers
30 views
Slight change of query causes CONVERT of illegal values to be done first, causing a conversion error [duplicate]
I have a table MyTable which has columns OldVal, NewVal, Nme which are all VARCHAR(MAX)
Wherever Nme = MyValue, the OldVal and NewVal values will be "integers" IE a numeric value value stored as a ...
5
votes
2answers
202 views
default permissible datatype conversion matrix
Today I wanted to define a uuid of value 00000000-0000-0000-0000-000000000000. Being a SQL Server man, I'd usually...
select cast(0x0 as uniqueidentifier);
...but I'm in postgres world now so I ...
1
vote
1answer
48 views
Different Date format in predicate when comparing against a date or datetime
I have the following query:
SELECT CAST(MyDateTimeCol AS DATE) AS MyDate,
Col1,
Col2
FROM MyTable
WHERE MyDateTimeCol >= '2019-11-25' AND MyDateTimeCol < '2019-11-26'
...
0
votes
0answers
63 views
Explicit conversion to avoid the implicit conversion warning [duplicate]
I have a TVF function which has the below code in it.I stripped down the function and has the logic as shown below.
WITH X
AS (SELECT cis.ProductionStatus AS CurrentDiaryStatus,
...
0
votes
1answer
47 views
Cast string to decimal without rounding the decimal part
I'm having this problem when casting a column from string to decimal. The problem is that after I do the cast on the column, it writes it in the new table as decimal, but rounding the decimals. I don'...
2
votes
1answer
41 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
157 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
413 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
546 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
125 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
132 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
1k 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) ...
0
votes
2answers
461 views
Get time from Unix milliseconds timestamp
My MySQL version is 5.6; it has a FROM_UNIXTIME function. But it works for timestamps with 10 digits: 1447430881.
mysql> SELECT FROM_UNIXTIME(1447430881);
-> '2015-11-13 10:08:01'
Now ...
5
votes
2answers
248 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
284 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
28 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 ...
11
votes
2answers
284 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
2k 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
88 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
51 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
56 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
2k 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
12k 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
1k 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
18k 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
2k 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
11k 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
4k 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
179 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
964 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
113 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
126 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
69 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
177 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
2k 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
246 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
359 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 ...
5
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
937 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
358 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
235 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
112 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
4k 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 ...
7
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
639 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
94 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
313 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 ...
