Questions tagged [type-conversion]

For questions about converting one data type into another, or issues arising from that process.

Filter by
Sorted by
Tagged with
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 ...