Stack Overflow
Stack Overflow

Questions tagged [to-char]

The TO_CHAR function converts another data type (e.g. NUMBER, DATE) to a text. Use for questions about the use of this function.

The TO_CHAR function converts another data type (e.g. NUMBER, DATE) to text. It is usually used to format output data.

Sources:

215 questions
3
votes
2 answers

oracle sql TO_CHAR function is adding a trailing blank in some case

I have a table with a number column that I'm trying to format with the oracle sql function to_char. Depending on how the column is shown, the result get a trailing blank or not. SELECT '[',TO_CHAR(ABS(Balance), 'FM0000000000000000V00'),']', …
Pierre-Yves Bernard
  • 33
  • 4
3
votes
3 answers

TO_CHAR and TO_DATE giving different results.How to achieve the TO_CHAR functionality using TO_DATE?

SELECT TO_CHAR((select logical_date -1 from logical_date where logical_date_type='B'),'DD/MM/YYYY HH24:MI:SS') FROM DUAL; This Query returns 23/04/2016 00:00:00 o/p of select logical_date -1 from logical_date where logical_date_type='B'…
Rishi Deorukhkar
  • 179
  • 4
  • 15
3
votes
4 answers

How do I correctly compare dates using to_char?

I have a query similar to : SELECT STUFF FROM TABLENAME WHERE TO_CHAR(STARTDATE, 'DD-MON-YYYY') > '01-OCT-2015' My result set contains STARTDATEs that all are less than '01-OCT-2015' What am I doing wrong? Thanks so much!
Brian M
  • 31
  • 1
  • 1
  • 3
3
votes
1 answer

To_Char and T_Number alternate in SQL Server for formatting

What is the SQL Server alternate of To_CHAR([Column_Name], -987654321) To_Number([Column_Name],'999999999D99999900') ? Also, What I can predict from the syntax is that they both are being used for formatting. Then what is the difference between…
Zerotoinfinity
  • 6,290
  • 32
  • 130
  • 206
3
votes
4 answers

SQL Error: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

I am trying to have a result 'none' every time it gives me a null result. Right now it is giving me a 0 for a null result. How could I have a row show me 'none' instead of a 0 for a null result. I have tried TO_CHAR and TO_NUMBER for the sum and I…
Manual
  • 1,627
  • 5
  • 17
  • 20
3
votes
1 answer

Selecting floating point numbers in decimal form

I've a small number in a PostgreSQL table: test=# CREATE TABLE test (r real); CREATE TABLE test=# INSERT INTO test VALUES (0.00000000000000000000000000000000000000000009); INSERT 0 1 When I run the following query it returns the number as…
palacsint
  • 28,416
  • 10
  • 82
  • 109
3
votes
1 answer

TO_char returning slash value after converting a number to String

I am having a database column amount [Data type Number(32,12)].When i use to_char on the amount field i get a slash value appended in the output. When i directly used the value stored in the amount field ,i am getting the correct value select…
user1501147
  • 41
  • 5
3
votes
3 answers

Database-Independent to_char Function in SQLAlchemy

I am using SQLAlchemy to make database-independent querys. I am facing one issue with to_char function. Consider the simple query like: select to_char(id,'999') from xyz It's working on Postgres but MySQL doesn't support it. How can I make this…
asb
  • 910
  • 3
  • 10
  • 19
3
votes
1 answer

to_char() issue in PostgreSQL

I have a PostgreSQL table with a field named effective_date and data type is integer(epoch date). What I want to do is to select only the entries that have an effective_date of my choice (I only want to query by the month). My query is below and the…
Les_Salantes
  • 317
  • 6
  • 20
2
votes
2 answers

How to replicate week number that was generated using Redshift's to_char on Snowflake?

I'm trying to migrate a Redshift SQL script to Snowflake and trying to emulate week number on Snowflake. Below is the sample Redshift code: select cast(to_char('2020-01-06'::date, 'WW') as int) as week, cast(to_char('2020-01-06'::date,…
Majid Azimi
  • 5,575
  • 13
  • 64
  • 113
2
votes
2 answers

TO_CHAR and SSSS (hours past midnight)

In converting some code from Netezza to Snowflake, I've come across what seems to be a gap in Snowflake. The format is: TO_CHAR(,'SSSS') The parameter SSSS is defined as Seconds past…
dburley70
  • 21
  • 1
2
votes
3 answers

Filter by TO_CHAR() in the WHERE clause

I'm running into some unexpected behavior, which doesn't make any sense to me. I have a table with a TIMESTAMP(6) column. If I execute the following query: SELECT DISTINCT TO_CHAR(ssef.SS_TIMESTAMP, 'DAY') FROM SS_EDGE_FORECAST ssef I get the…
Giffyguy
  • 20,378
  • 34
  • 97
  • 168
2
votes
2 answers

TO_Char number format model in Oracle

I don't understand fully how can I use the to_char function to convert a number to a string with the appropriate format model. The actual number has this type of format: Uses comma as decimal separator Always 5 decimal numbers The integer numbers…
Ponzaro
  • 139
  • 2
  • 5
  • 16
2
votes
1 answer

how to fix oracle ORA-01722 invalid number error

I have Oracle db with these inputs on column1(NUMBER(22,6)) in myTable. 0 199935,15 1026299 I want to display these columns like that: 00000000000000000.000000 00000000000199935.150000 00000000001026299.000000 My query: SELECT…
Pronto
  • 179
  • 1
  • 2
  • 11
2
votes
1 answer

postgresql to_char an integer with German format

I'm looking to format a float so that it will be displayed in the following way: 999.999.990,00 I'm currently using the to_char function: to_char(SUM(amount::float)/100, '999G999G990D00') However this format is showing commas instead of dots.…
OAK
  • 2,994
  • 9
  • 36
  • 49
1
2
3
…
14 15