Questions tagged [type-conversion]
For questions about converting one data type into another, or issues arising from that process.
167
questions
1
vote
3answers
84 views
Implicit conversion: when does it hurt performance a lot?
I know that when data types are the same it is good and it hurts performance when they are not.
But sometimes it seems to have very little impact and basically a waste of time to fix it. Other times ...
0
votes
1answer
18 views
Need help identifying how to make a function selectable using table() clause and a custom data type
I would like to be able to run the following code to select records:
Desired Syntax
select * from table(my_new_package.get_data(123))
I have been successful in creating code that compiles, but I am ...
5
votes
2answers
1k views
PostgreSQL - CAST vs :: operator on LATERAL table function
While I can
SELECT elem[1], elem[2]
FROM ( VALUES ('1,2'::TEXT) ) AS q(arr),
LATERAL CAST(String_To_Array(q.arr, ',') AS INT[]) AS elem
;
using an explicit call to CAST, I can't
SELECT elem[...
-2
votes
1answer
57 views
Convert Varchar to Datetime
I have a database field Column_A which is in Varchar(16) not null and the values are for example 20191210215033CS but I want to break it like 2019-12-10 21:50:33
Since it is a production database ...
4
votes
1answer
240 views
SQL Server with French_CI_AS Collation date conversion
I have a French installation of SQL Server using French_CI_AS Collation. For legacy reasons, a table column stores data as VARCHAR which is later converted to the right type, akin to variant in C++.
...
0
votes
2answers
77 views
Do user defined data types always cause implicit conversion to occur when fields of those UDDTs are used as predicates?
I have a database (from a vendor application) that for some unknown reason has a UDDT on almost every column, even when the UDDT is logically the same as the underlying type it implements. E.g. a ...
0
votes
1answer
56 views
PostgreSQL 9.6.17 COALESCE function error
I'm trying a SQL query with the function COALESCE in PostgreSQL 9.6.17, but it produces an error:
invalid syntax for type double precision: ""
Example query:
SELECT COALESCE(date_part('...
0
votes
0answers
44 views
Convert MySQL table date/datetime column to int (unix_timestamp) with existing records
I have tables with existing data, that use date? and datetime` fields, that I want to convert to unix timestamp values (ints).
For some of the tables, creating a view that calls unix_timestamp() on ...
-2
votes
1answer
27 views
ALTER DATABASE MODIFY FILE fails - I want to alter data file sizes using size = @variable - not working
Example:
DECLARE @sql NVARCHAR(255)
DECLARE @DBname NVARCHAR(128), @FileName NVARCHAR(128), @NewSizeMB INT
SET @DBname = 'AGTestDB'
SET @FileName = 'AGTestDB'
SET @NewSizeMB = 1024
SET @sql = N'...
0
votes
4answers
419 views
Why does SQL Server say it can't convert varchar to numeric?
I have the following schema/data in MSSQL Server 2019 (SQLFiddle):
CREATE TABLE products(
idn NUMERIC(9) PRIMARY KEY
);
CREATE TABLE sales(
idn NUMERIC(9) PRIMARY KEY,
pid VARCHAR(50) ...
5
votes
3answers
558 views
how to solve this warning in select
I have a problem with this query:
SELECT *
FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID--- adventureworks db
Warnings
Type conversion ...
2
votes
1answer
144 views
Get date from SQL Server audit filename
SQL Server audit filenames use the following pattern:
Name_GUID_PartitionNumber_Timestamp.sqlaudit
An example filename below:
Audit-ObjectChanges_2D94C1E8-CCAD-405F-A9F2-...
0
votes
1answer
250 views
Mongo: how to covert a string to decimal?
I have written millions of documents with $CURRENT_CASH_BALANCE as strings instead of decimals. How can I convert them all to decimals?
0
votes
3answers
35 views
Convert two integers into timestamp
How do I convert two integers, one for date and one for time, into a timestamp?
Given 20200304 and 1046, return a timestamp for April 3, 2020, 10:46 AM.
Given 20200304 and 946, return a timestamp for ...
1
vote
1answer
63 views
out of all columns in a table, how can I find the one datatype that is cheapest and I could convert all other columns datatypes into?
I sometimes have to unpivot tables, that means, converting all columns into rows.
Generally that becomes a table with 2 columns (just to simplify things) - the first is the column name and the second ...
0
votes
1answer
41 views
How can I output a legible timestamp from “1583981745.335346” (stored in a 'double' field type) via a select statement?
I have a table within a WordPress installation that stores timestamps in a format that I do not recognize, nor can I figure out how to translate it. In the MySQL database, it appears as "1583981745....
0
votes
1answer
35 views
MySQL: Use index in background query
I'm using MySQL 5.7.10
I'm checking a new query for an audit report.
I'll execute it in a simple background Unix process, whick invoke mysql from the console.
To check the query, I use a worksheet in ...
0
votes
1answer
92 views
converting latin to utf8mb4 causes questionmarks
The original format of the data is unknown
The new table is in utf8mb4_general_ci
If I do CONVERT(BINARY CONVERT(column USING latin1) USING UTF8) as mentioned here - it fixes all text, but converts ...
1
vote
1answer
628 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
31 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
92 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
267 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
261 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
69 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,
...
2
votes
2answers
1k views
How to bulk convert ISO8859 text in Text columns to UTF8 in nvarchar(max) columns?
I've been struggling for days with a problem on a MS SQL database. Here is the situation: I have an old Open Source PHP application (Limesurvey version 1.92) running on a Windows 2008R2 server, with ...
0
votes
1answer
148 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
61 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 ...
1
vote
1answer
2k views
Convert sql_variant to varchar to select left 2
I am trying to populate a column (versionstatus) with if the service pack needs updating. I have created a lookup table which lists the SQL Version, and the latest product version, e.g.
SQLVersion ...
2
votes
2answers
171 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
425 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
1k 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
133 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
227 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
3k 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) ...
1
vote
1answer
140 views
Convert Date and Filter by Year
I have a date field that is in a 'yyyymmdd' format, and I need to filter the query by the year, using that field.
For example, in my table there is a "Posting Date" field that is in the '20180401' ...
2
votes
2answers
2k 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
255 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
,...
0
votes
1answer
414 views
Try_Convert Datetime showing '1/1/1900', want to show space
How do I get try_convert datetime to display NULL when the input is space?
I am getting two different results here. My intention is to show 'space' number query as Null also, just like first query.
...
2
votes
1answer
1k 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
2answers
290 views
Fix OGR2OGR “failed when converting the varchar value to data type int” issue in the MSSQL table -
I am a newbie to MSSQL and am trying to convert an MSSQL table to Mapinfo Tab file using OGR2OGR but it keeps failing with the above message on a few tables.
I have tried to get a reply in the GIS SE ...
-1
votes
1answer
3k views
Convert SQL Date MMDDYY varchar to Date?
In reading this,
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
http://www.sqlusa.com/bestpractices/datetimeconversion/
I cannot locate the ...
0
votes
1answer
171 views
MS SQL -Date and Time Style conversion problem
I need to convert Date and Time Style from USA format to German and than cast that into int data type.
So for variable in case below this works fine if I want to display it in Standard 112 (ISO)
...
1
vote
2answers
1k views
Converting Varchar to Int error
I came across an issue today, and while I was able to fix it, I am unsure what caused the issue to happen.
I have a column that is Varchar(20). It is used to store just year data (2010, 2011, etc.) ...
11
votes
2answers
502 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
7k 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
121 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
70 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
100 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 ...
