Questions tagged [cast]

Converts an expression of one data type to another.

Filter by
Sorted by
Tagged with
4
votes
4answers
114 views

Error converting VARCHAR(MAX) to XML due to “UTF-8” attribute

I need to dig into a logs table with a schema similar to this: CREATE TABLE t ( id int PRIMARY KEY, data varchar(max) ); Column data stores a XML text received from a web service in this format: ...
0
votes
1answer
153 views

Performance issues with CAST function

Observing this performance issue that can't be explained with EXPLAIN in a PostgreSQL 9.6 database. There is currently a table public.users, which has a cast on it to output a record to JSONb. (e.g. ...
1
vote
1answer
25 views

What is the best way to divide data for random groups?

I would like to have persisted column which will randomly group data for 32 groups according to one of varchar key column in table. My idea was: SELECT ABS(CAST(HASHBYTES('MD5',[keyColumnFromTable]) ...
1
vote
1answer
48 views

PostgreSQL ignores implicit casts in some situations

The setup (tried only on PostgreSQL 9.6): CREATE TYPE MY_ENUM AS ENUM ('a', 'b'); CREATE CAST (CHARACTER VARYING AS MY_ENUM) WITH INOUT AS IMPLICIT; CREATE TABLE t (x MY_ENUM); INSERT INTO t VALUES ('...
1
vote
1answer
96 views

How to prevent implicit casting from bytea to text during the current connection in postgres?

In Postgres bytea values are automatically converted to text when inserted to text/varchar columns, based on the bytea_output setting. I am working with some program code which automatically converts ...
21
votes
3answers
36k views

Determine percentage from count() without cast issues

I'm trying to run the following query to provide the % of rows in my patients table that have a value the refinst column. I keep getting a result of 0. select (count (refinst) / (select count(*) from ...
-1
votes
1answer
68 views

MySQL Cast to TINYINT [closed]

I'm trying to cast a string data to a TINYINT data but I'm getting below error. SELECT CAST('100' as TINYINT); > 1064 - You have an error in your SQL syntax; check the manual that corresponds to ...
0
votes
2answers
3k views

Does CAST(DateTime AS DATE) cause the entire table to be CAST before comparison? [duplicate]

This is a very common need in the WHERE clause of our SQL statements: CAST(Something.CreationDate AS DATE) BETWEEN CAST(@firstDate AS DATE) AND CAST(@lastDate AS DATE) All of the fields are of ...
5
votes
2answers
2k views

Is it possible to change the default style used by CAST/CONVERT when converting DATETIME? [closed]

When you attempt to CAST a DATETIME as VARCHAR in SQL Server, the default date formatting style is a fairly inaccurate date format that loses the precision of both seconds and milliseconds. For ...
0
votes
1answer
46 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 ...
1
vote
3answers
3k views

CASE with CHARINDEX and CONVERT

I have a column of characters in SQL Server 2014 that I have to convert to numeric and give them the opposite value (- to + and + to -). The data looks like this: -0000000000002999 ...
7
votes
3answers
20k 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
1answer
14k 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 ...
-1
votes
1answer
2k 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 ...
2
votes
4answers
11k 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 (...
0
votes
1answer
157 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
1answer
73 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' ...
5
votes
2answers
12k 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 ...
0
votes
1answer
24 views

Convert a numeric column with number eg.(-1222222) to two float column with two decimal places eg (-12222.22)

I know there are different variations of this question asked. But what I am trying to do is that, there is a table with a numeric column (12,0) that has data looking like Units ----- -1222222 0 -19 ...
1
vote
2answers
19k 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 ...
2
votes
2answers
32 views

Where can I read about the behavior of the postgress casting operators?

I want to do something like this: alter table foos alter column bar type bool using bar::boolean; bar is currently a text column. Some experimentation shows that postgres will do things like convert ...
8
votes
2answers
269 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,...
-4
votes
1answer
33 views

can I be helped? my querey should = something like .000534 however I get 0

My query result should be 0.000534, but I'm getting 0. Please help me. psql -Upostgres -dcans -c \ 'select (select COUNT(*) FROM brd.gaf_info where ...
1
vote
2answers
646 views

Why isn't it possible to cast to a timestamp in an index in PostgreSQL?

I am working with a JSON document which I receive from an external source. In this document, there is a timestamp property which corresponds to the textual format of a timestamp PostgreSQL data type. ...
0
votes
1answer
36 views

Postgres - how to find contents of one field within another field

How do I find where the contents of one field are within another field? I have a database table with a auto-generated sequence number in one field, and another character based field which should ...
20
votes
1answer
23k views

Querying JSONB in PostgreSQL

I have a table, persons, which contains two columns, an id and a JSONB-based data column (this table has just been made for demonstrational purposes to play around with PostgreSQL's JSON support). ...
7
votes
2answers
7k views

How to create an index on an integer json property in postgres

I can't figure out for the life of me how to create an index on a property of my json column which is an integer. I tried it this way (and also dozens of others) CREATE INDEX user_reputation_idx ON ...
0
votes
1answer
66 views

Two conversions of practically the same number giving two different results

To my surprise, I run the following queries in SQL Server 2014 and each returns a different result, as shown in this db<>fiddle... This Select convert(numeric(19,2),convert(float,9....
2
votes
1answer
209 views

Is TEXT to VARCHAR(MAX) an implicit conversion?

I am copying TEXT columns to another table where the destination column is VARCHAR(MAX). It appears that this is an implicit conversion and that I do not need to write an explicit CAST for it. Is ...
0
votes
1answer
194 views

Casting fraction number to decimal throws Arithmetic overflow error

I have a table called AssignmentMarks that stores Student's assignment marks for different subjects. This table has a column called Marks varchar(10) which stores the marks. The marks can be like 1, ...
0
votes
1answer
75 views

PostgreSQL: What is the best way to cast from from text to point[]?

I'm looking for a way to cast from a text value: '(180,90),(-180,-90)' into a point[] value: {"(180,90)","(-180,-90)"} What is the best way to do this? One possibility that I can think of is to ...
0
votes
2answers
69 views

SQL USING CAST in a query inside a stored Procedure

I was trying to use a query inside an Stored procedure which has a cast funtion. But when executing the SP I'm getting an error Conversion failed when converting date and/or time from character ...
0
votes
0answers
174 views

Writing CSV directly to a BYTEA column from PostgreSQL tables

Assuming these two tables: CREATE TABLE data (day DATE, kpi INTEGER); CREATE TABLE reports (csv BYTEA); Is it possible to (pseudocode): INSERT INTO reports (csv) (SELECT to_csv(SELECT day, kpi FROM ...
3
votes
2answers
216 views

UPDATE with CAST(meta->>“$.field”) failing due to “null”

This issue is affecting MySQL 5.7.25. So, in a migration I'm moving legacy fields from the table into a JSON column. This part of the process works fine. But, the rollback is failing miserably! At ...
1
vote
2answers
1k 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
4k 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 ...
2
votes
1answer
308 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 ...
10
votes
1answer
3k 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" =...
9
votes
2answers
2k 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 ...
2
votes
2answers
12k views

Getting “Conversion failed when converting the nvarchar value to data type int.”

I have a SQL Server table pairing zipcodes and lat/long geometry, such as below ID ZIP5 geom 1 356HH 0xE610000001044E00....FFFFFFFF0000000003ID 2 35677 0xE6100000010404000000068....000003 ...
1
vote
0answers
74 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
113 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
669 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 ...
6
votes
3answers
14k views

changing float->numeric casts from assignment to implicit, dangerous?

In porting an application to PostgreSQL (9.1), one odd SQL incompatibility I've discovered concerns the round() function, specifically the version that takes a second argument indicating the rounding ...
-3
votes
2answers
445 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....
1
vote
0answers
612 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
47 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 ...
2
votes
1answer
2k 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
1answer
186 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 ...
2
votes
1answer
117 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:...