Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [cast]

Converts an expression of one data type to another.

1
vote
2answers
46 views

How to cast (a) a date value recorded as a VARCHAR to (b) a specific DATE format for (c) use in an ORDER BY clause

How would I cast a column value (a "non-MySQL" DATE string retained as a VARCHAR) to a "MySQL date" so it can be used for sorting? I have dates in the format of 06-02-2019 (6 feb) but MySQL itself ...
2
votes
2answers
134 views

Cast as int in CASE expression in SQL

I have a query that is pulling data from 10 tables and a sub-query. One of the selects is for a location and I am using a case expression to clean up the data on output. The field it is pulling the ...
0
votes
1answer
18 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) ...
9
votes
1answer
222 views

Why do I need to cast NULL to column type?

I've got a helper that's generating some code to do bulk updates for me and generates SQL that looks like this: (Both the active and core fields are of type boolean) UPDATE fields as t set "active" =...
2
votes
2answers
671 views

Casting an array of texts to an array of UUIDs

How can I cast an array of texts into an array of UUIDs? I need to do a join between two tables: users and projects. The users table has an array field named project_ids containing the project IDs ...
1
vote
0answers
38 views

Used a CAST function ,still IMPLICIT Conversion warning in Execution Plan

I know that IMPLICIT Conversion warnings occur when the SQL server has to convert a value to a another datatype to do some processing. I am getting this warning in an execution plan and when i checked ...
1
vote
0answers
60 views

Is it possible to define a cast between text and a domain representing a uuid to avoid manual casts?

I created a DOMAIN as following: CREATE DOMAIN country_id AS uuid; I create such domains for readability. It is much easier to read function definitions which accept a country_id instead of a uuid. ...
6
votes
2answers
95 views

Is there a way to partially initialize a TYPE?

I have created a custom type: CREATE TYPE my_type as(name text, street text, location text); Initializing such a type is easy: SELECT ROW('a', 'b', 'c')::my_type; I am wondering if there is an ...
1
vote
0answers
99 views

Update BIT field with non-BIT value

I was trying (by a mistake) to update bit typed field with non-bit value. It took me some time to realize the cause for not getting this field updated (non-bit value). I was supprised that no native ...
2
votes
1answer
32 views

C++ stoi equivalent in SQL Server's T-SQL

I'm looking for something I can use in Microsot SQL Server that would act the same way the stoi function in C++ acts. Specifically, I'm looking to be able to grab numerics at the beginning of the ...
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 ...
0
votes
0answers
15 views

Conversion Failed From nvarchar to int even when all values in result set are int [duplicate]

Take the following SQL sample: Declare @Table Table (Value int) Insert Into @Table Select intValueAsVarcharType From OtherTable Where Category = 'Type1' The OtherTable.intValueAsVarcharType ...
3
votes
3answers
605 views

Can MariaDB or MySQL cast as bigint, getting an error: … right syntax to use near 'bigint)'?

If I want to take a string and create a table (this fails in strict mode), CREATE TABLE g AS SELECT CAST('2147483699' AS int); How do I tell it that I want a bigint type? I'll also get an int(10) ...
0
votes
2answers
730 views

COALESCE with cast

I'm pulling data from a jsonb column in Postgres, and casting it to an integer to compare it against a count. The problem is that in some cases the data apparently can't be cast to integer and I get: ...
0
votes
1answer
26 views

In PostgreSQL, what is the behavior of the bit(n) -> “char” cast?

Using PostgreSQL if I cast 65::bit(8)::"char" I get 0, if I however cast 65::bit(8)::int::"char" I get A as I would expect. SELECT v, v::bit(8)::"char" AS noint, v::bit(8)::int::"char" AS "...
5
votes
4answers
676 views

PostgreSQL alternative to SQL Server’s `try_cast` function

Microsoft SQL Server has what I consider a remarkably sensible function, try_cast() which returns a null if the cast is unsuccessful, rather than raising an error. This makes it possible to then use ...
0
votes
1answer
365 views

Average Value not working

I have a database of students and grades across varying report cycles. Each Report Cycle has a unique ID and each subject also has a unique ID. Students can have more than one teacher per subject so I ...
0
votes
1answer
207 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 ...
0
votes
1answer
135 views

Alter table with casting

Is there a way of run an alter table command with cast on MySQL databases? I know that is possible to do it with Postgresql and Sql servers, but I didn't find anything about it when searching the ...
2
votes
3answers
2k views

float to varchar with no trailing zeros

I am having a heck of a time trying to convert a FLOAT(53) field to a VARCHAR where there are no trailing zeros or scientific notation. The field could have a number that is really big or really ...
3
votes
1answer
678 views

Define calculated column of BOOLEAN/TINYINT(1) type in a View? [duplicate]

I'm configuring a View which contains calculated columns that should be returning Boolean results. Here's an anonymised version of the view: CREATE VIEW `ItemView` AS SELECT Item.ID ,...
0
votes
1answer
39 views

Special letter behaviour problem?

I asked question in very blurry manner because I cannot explain in short sentence what is my problem about. In general I have a set of procedures which transforms text and XML. Inside the procedures ...
8
votes
1answer
17k views

Decode Base64 String Natively in SQL Server

I have a varchar column in a table in SQL Server which hold a base64-encoded text string which I would like to decode into it's plain text equivalent Does SQL Server have any native functionality to ...
2
votes
1answer
409 views

PostgreSQL Full Text Search (FTS), what is the difference between ::tsvector and to_tsvector()?

i have a table with tsvector type column (col). I fill some rows via script and use cast data::tsvector, but (!) some rows was filled with to_tsvector(data), so i have rows with different values like ...
-3
votes
2answers
196 views

SQL Server: How to set time in a human readable format

I found this useful query here and I use it to monitor SQL Server jobs. SELECT sJobHis.[server], sJobStep.database_name, SJob.name, SJob.enabled, CASE WHEN SJob.enabled = 0 THEN '0' WHEN sJobStep....
4
votes
1answer
2k 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 ...
-1
votes
1answer
1k views

postgresql function CAST json ARRAY to A TYPE

I try to cast a JSON to a TYPE VOID_EVENT_CREATE, but I didn't work because of the array. I'm going to use the query with NodeJS that why I would like to cast the JSON to a special type. It's easier ...
6
votes
1answer
9k views

PostgreSQL custom operator UUID to varchar

I have a rather complicated Postgres database in which many UUID fields were incorrect stored as VARCHAR. I'd like to migrate them over in piecemeal, but unfortunately, doing so breaks all my views as ...
1
vote
1answer
509 views

Proper way to create an integer jsonb nested key index

I have the following index I'm trying to modify by declaring the poLineNumberInteger key as numeric/integer instead of text. This is valid syntax: CREATE INDEX ubdrecord_jsonb_extract_path_text_idx ...
2
votes
1answer
91 views

Ranges over domains do not trigger implicit casting

As far as I was able to find out, it is currently not possible in PostgreSQL v9.5 (and above?) to obtain implicit casting with custom range types. To illustrate, when I define the following demo setup:...
2
votes
1answer
190 views

How does cast work with Set Returning Functions (SRF) like generate_series?

I got to wondering about how we would best use generate_series if we only want a series of dates. As a note, generate_series returns setof timestamp or setof timestamp with time zone (same as argument ...
2
votes
1answer
48 views

Why do PostgreSQL examples say `+ integer '7'`? [closed]

The PostgreSQL date functions documentation contains the following example: date '2001-09-28' + integer '7' Why does it consist of converting a string to an integer? Why not phrase it like this: ...
1
vote
1answer
882 views

Why is to_char left padding with spaces?

When ever I use 099 indicating 3 digits 0-padded, I'm getting spaces on the left side. SELECT '>' || to_char(1, '099') || '<'; ?column? ---------- > 001< (1 row) Why is to_char left ...
2
votes
1answer
592 views

PostgreSQL parses string literal as record before calling cast

In my PostgreSQL 9.6 database, I have a composite type, let's call that type widget. I have a PL/pgSQL* function called text_to_widget that parses textual representations of widgets to widget values, ...
4
votes
2answers
7k views

Error converting NVARCHAR to BIGINT when in SUBQUERY with WHERE clause after invalid records filtered [duplicate]

I have a table that has an NVARCHAR column that contains data that cannot be converted to BIGINT. I'm well aware of that and have filtered it out using ISNUMERIC(BB.NVARCHARCOL) = 1. Despite this, I ...
7
votes
2answers
810 views

How exactly does the one-byte “char” type work in PostgreSQL?

I often see people talking about "char". I've never used it. It's defined in the docs as, The type "char" (note the quotes) is different from char(1) in that it only uses one byte of storage. It is ...
8
votes
2answers
200 views

Why does this seek on BIGINT col have extra constant scan, compute scalar, and nested loops operators?

When I look at the actual exection plan of some of my queries I notice that literal constants used in a WHERE clause show up as a nested chain of calculate scalar and constant scan. To reproduce this,...
0
votes
1answer
284 views

how to get back the modified INOUT parameter in postgres

I have two functions with a similar signature, lets say: CREATE FUNCTION func1(INOUT new "My_Table") AS $$ ... $$ LANGUAGE plpgsql; CREATE FUNCTION func2(INOUT new "My_Table") AS $$ ... $$ LANGUAGE ...
3
votes
4answers
6k views

Format value to configured number of decimal places

I want to format a number to display decimal places according to a configuration value specified in another table. If the configured value is NULL, the number of decimal places should default to 2 (...
3
votes
3answers
532 views

How do I cast varchar to macaddr without changing client code

I have a dedicated DB Server with Debian Jessie and Postgresql 9.4 Client: A is a Tomcat Java app which use torque with a JDBC driver connecting to the DB Server. Issue: on Updating a row with a new ...
5
votes
2answers
548 views

Can you use an inserted variable in a default constraint

Working in SQL Server, let's say I have a database with two tables: events and competitors. events has the following columns: [event_id] [event_name] [winner_id] [loser_id] competitors has the ...
0
votes
1answer
119 views

Going from long/lat cords to Geometry or Geography type

When I make a query: select *, concat(pickup_longitude,' ',pickup_latitude) as pickup_pt, concat(dropoff_longitude,' ',dropoff_latitude) as dropoff_pt it usually defines the ...
1
vote
0answers
53 views

What is the safest method for order by cast() [duplicate]

My query looks a bit like this: Select top (@n) * From MyTable Where IsNumeric(MyColumn) = 1 Order By Cast(MyColumn as int) desc Occasionally Sql will produce a query plan that places the Sort ...
1
vote
2answers
518 views

Cast varchar into int, converting any value under 0 to 0

I am inserting values form a staging table in SQL Azure into a production table. Values in the staging table are varchar, while the target value in the production table is tinyint (it is an age field ...
7
votes
3answers
13k views

Inserting date from string: CAST vs CONVERT

Consider the following two ways to convert a datetime varchar string into a date field: SELECT convert(date, '2012-12-21 21:12:00', 20) -- Only date is needed SELECT cast('2012-12-21 21:12:00' as ...
1
vote
3answers
113 views

Raw cast of arbitrary string to integer [closed]

I have non-numeric string IDs in a table, and would like to cast them to unique integers. I need the mapping to be invertible / one-to-one. The string IDs are of uniform length. I want to do this by ...
1
vote
2answers
9k views

casting empty string as null datetime

Consider the following code in Microsoft SQL Server 2012: INSERT INTO [dbo].Production SELECT [field1] ,[field2] ,cast([datefield] as datetime) FROM [RAW].Staging The staging table is ...
1
vote
1answer
5k views

ERROR: Arithmetic overflow error converting float to data type numeric

I unfortunately have some bad data and need to work around it. I have a Parcel column in a spreadsheet with some parcels that have inadvertently been converted to floating point numbers. I posted a ...
3
votes
1answer
299 views

PostgreSQL CAST() giving unexpected results

I can't seem to figure out how this is rounding overflow digits. It is most definitely not any of: Round up if last digit is 5 or higher. Round to even if last digit is 5. Randomly round up or down ...
4
votes
1answer
6k views

Handling sql column with mixed varchar and float data types

We have some Excel spreadsheets with parcel numbers for properties that should be in a standard varchar format similar to: [Parcel # 1] [Building Address] 038-60-171-16-000 1750 W 4th St ...