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
0 votes
1 answer
421 views

Why SQL Server returns conversion error when mixing different data types in bulk INSERT statement?

I'm using Microsoft SQL Server, the version doesn't really matter, because it seems to work the same on most of the versions. It's easier to show an example than explain: CREATE TABLE form ( id ...
Stalinko's user avatar
  • 181
0 votes
0 answers
18 views

Combine INT, TIME fields and compute Elapsed Percentage

Need calculation and conversion help... Columns: [Days] INT [hh:mm:ss] Time [AllowedRuntimeMinutes] INT Need to combine [Days]+[hh:mm:ss] as the duration the query has been running, and then calculate ...
CrushingIT's user avatar
0 votes
0 answers
119 views

Using CONVERT for datetime 24 hours with style codes undocumented for?

Using CONVERT for datetime whose time part is 24 hours - works. Examples with style codes 101-105: SELECT CONVERT(datetime, '05/24/2023 23:30', 101) SELECT CONVERT(datetime, '2023.05.24 23:30', 102) ...
HeyJude's user avatar
  • 437
2 votes
1 answer
835 views

Converting varchar to decimal with truncate

I have a column called latitude which is currently varchar(20) I want to convert it to DECIMAL(9,6) However the data stored inside the column is greater than 6 decimal points i.e. 48.123456891123456 ...
Quade's user avatar
  • 181
1 vote
1 answer
229 views

TRY_CAST fails when fed very long string... but only from column or variable

I've recently encountered this weird behavior of TRY_CAST function, and I wonder what's going on here. (It feels like bug, but most of such bugs I have encountered in past were actually intentional.) ...
J. Doe's user avatar
  • 130
2 votes
2 answers
183 views

Using TRY_PARSE in SQL Server 2008

I have a script in SQL Server 2012 like this: Declare @ParamSPKDateFrom varchar(50), @SPKDateFrom date, Select @SPKDateFrom = TRY_PARSE(@ParamSPKDateFrom AS date USING 'id-ID') Can I convert this ...
jubey simanjuntak's user avatar
0 votes
1 answer
920 views

Convert an Int column to BigInt in a stored procedure in SQL Server 2012

We have a stored procedure that uses SUM on an Int column in the SELECT statement. We have a few cases where for one of the parameter values, the SUM(integer column) exceeds the Int range and ...
lifeisajourney's user avatar
7 votes
1 answer
1k views

Why does TRY_CAST throw an error when attempting to convert VARCHAR to UNIQUEIDENTIFIER?

I have a VARCHAR(MAX) column in a table that can store data in many different formats, including the GUID format. I tried the following code when joining a UNIQUEIDENTIFIER column in another table: ...
Joe Obbish's user avatar
0 votes
1 answer
396 views

MySQL incorrect equality casting bigint to char

Why is the following statement true in MySQL? CAST(544553000004545482 AS CHAR) = 544553000004545446 --Background-- I am querying a database with user IDs stored as VARCHAR(255), but my program ...
Eric's user avatar
  • 3
-1 votes
3 answers
5k views

How to select Max date that is valid?

I have a date as varchar (dont ask why). I need to select Max(date). However, date can be faulty, like 2034-34-34. I would like to select max date that is valid and not greater than today. It's t-sql. ...
Hrvoje T's user avatar
  • 133
1 vote
1 answer
229 views

How to retain same FLOAT value after converting it to a VARCHAR in Azure Synapse

Consider the following example: DROP TABLE dbo.TEST_FLOAT; CREATE TABLE dbo.TEST_FLOAT ( AMOUNT FLOAT ); INSERT INTO dbo.TEST_FLOAT (AMOUNT) VALUES (11546.25); SELECT AMOUNT, CAST(AMOUNT AS ...
Lauren_G's user avatar
0 votes
1 answer
4k views

Strange Error ... Conversion failed when converting the varchar value 'www.com' to data type int

The issue that I am running into is happening on Microsoft SQL Server. I have a view that is unioning together 2 tables. It is returning a little over 7 million records. When I attempt to JOIN ...
steveb's user avatar
  • 33
3 votes
1 answer
98 views

How to find type mismatches (implicit conversion) between sprocs and tables?

I have a sproc that has these lines DECLARE @newTimestamp datetime = CURRENT_TIMESTAMP UPDATE dbo.Accounts SET Modified = @newTimestamp However, in the Accounts DB, we have this [Modified] ...
mfinni's user avatar
  • 145
7 votes
3 answers
1k views

Alternate Solution For REPLACE() In ntext Column

Overview I have a picture database that catalogue information of pictures taken with cameras. The database contains a table pictures which has 256 columns which contain information about the picture ...
John K. N.'s user avatar
  • 17.4k
5 votes
1 answer
655 views

Standard Deviation for Times

Is there a T-SQL coding best practice to get an accurate standard deviation value for a group of times? STDEV doesn't like the time data type. I'm thinking that maybe a conversion to minutes as an ...
AccidentalDBA_CO's user avatar
1 vote
1 answer
38 views

Convert specific time format to common one

Environment: I have 'duration' datas stored as varchar(4) in a table. 2 first characters are hours, 2 last characters are minutes. E.g.: 0100 means 01:00 0456 means 04:56 and so on... What I need: ...
EdenSource's user avatar
3 votes
3 answers
1k 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 ...
xhr489's user avatar
  • 795
0 votes
1 answer
228 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 ...
Eneerge's user avatar
  • 23
6 votes
2 answers
4k 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 ...
geozelot's user avatar
  • 183
-2 votes
1 answer
358 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 ...
SQL_NoExpert's user avatar
  • 1,039
4 votes
1 answer
2k 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++. ...
Niraj Srivastava's user avatar
0 votes
2 answers
123 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 ...
J.D.'s user avatar
  • 35.2k
0 votes
1 answer
699 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('...
Иван Линюшин's user avatar
-2 votes
1 answer
267 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'...
user188616's user avatar
0 votes
4 answers
5k 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) ...
Nishant's user avatar
  • 849
5 votes
3 answers
741 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 ...
Benyamin Mohamadi's user avatar
3 votes
1 answer
772 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-...
boomcubist's user avatar
1 vote
1 answer
3k 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?
Ryan Scott's user avatar
0 votes
3 answers
539 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 ...
Sarov's user avatar
  • 279
1 vote
1 answer
83 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 ...
Marcello Miorelli's user avatar
0 votes
1 answer
111 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....
user2461674's user avatar
0 votes
1 answer
181 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 ...
yaki_nuka's user avatar
  • 141
0 votes
1 answer
713 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 ...
stackbox's user avatar
1 vote
1 answer
3k 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 ...
Hellion's user avatar
  • 356
0 votes
1 answer
36 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 ...
J.D.'s user avatar
  • 35.2k
2 votes
0 answers
272 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 ...
tomka's user avatar
  • 927
1 vote
0 answers
31 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 ...
SE1986's user avatar
  • 1,928
6 votes
2 answers
654 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 ...
Peter Vandivier's user avatar
1 vote
1 answer
967 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' ...
SE1986's user avatar
  • 1,928
0 votes
0 answers
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, ...
user9516827's user avatar
  • 1,305
2 votes
2 answers
4k 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 ...
P Daubresse's user avatar
0 votes
1 answer
1k 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'...
Carlos Delgado's user avatar
2 votes
1 answer
774 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 ...
KeetsScrimalittle's user avatar
2 votes
1 answer
9k 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 ...
jack's user avatar
  • 369
2 votes
2 answers
446 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 ...
s.demuro's user avatar
  • 303
2 votes
1 answer
436 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] ...
Merveille Tchouda's user avatar
6 votes
1 answer
2k 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: ...
sgargel's user avatar
  • 337
4 votes
1 answer
146 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 ...
Hannah Vernon's user avatar
  • 69.7k
3 votes
2 answers
827 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(...
SE1986's user avatar
  • 1,928
0 votes
1 answer
5k 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) ...
jack's user avatar
  • 369