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
1
vote
1 answer

How to average a score in groups based on the month and year in SQL

I have a table called reviews. Reviews has two columns I care about: score (int) and created_at (datetime). Imagine the following…
ArchieJG
  • 13
  • 3
1
vote
0 answers

PostgreSQL to_char() doesn't work as expected

This is my Schema down below And i have to solve this question "How can you produce a list of the start times for bookings for tennis courts, for the date '2012-09-21'? Return a list of start time and facility name pairings, ordered by the…
sakib 1212
  • 60
  • 6
1
vote
2 answers

to_char() problem decimal points and thousand separator

I'm trying to convert a number to a string and apply a format using to_Char in an Oracle database. This is what I'm after (example of the value on the left and what I after on the left): 0 --> 0 0.29 -->…
Kresten
  • 810
  • 13
  • 36
1
vote
1 answer

hive 3.0.0 want to convert timestamp to string with a mask

I have to revert a date conversion At the beginning it was CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(t0.date_creation , 'yyyyMMdd'T'hhmmss')) AS TIMESTAMP) Now I'm looking on how to convert a timestamp to the format 'yyyyMMdd'T'hhmmss' the syntax…
Jeannot77680
  • 49
  • 6
1
vote
1 answer

Converting a negative number in oracle to Hexadecimal is giving wrong output?

I am using below select statement to convert a positive number to hexadecimal SQL>select to_char('2122699925', 'XXXXXXXXXXXXXXXX') from testable Output : 7E85D495 But the TO_CHAR for negative number it is giving me output as…
vishal agrawal
  • 45
  • 3
1
vote
1 answer

Why is 0 an exception for to_char(0,'B9999')?

What makes the number 0 an exception to Oracle to_char(number,'B9999') mask? In this query 0 doesn't print at all. How can I left pad all my numbers including 0 with spaces? Is lpad() the only alternative? select to_char(0,'B09999') as num_fmt from…
zundarz
  • 1,540
  • 3
  • 24
  • 40
1
vote
1 answer

Assigning TO_CHAR result to VARCHAR2, NVARCHAR2

1) In Oracle, can I store result of TO_CHAR to VARCHAR2, NVARCHAR2? It seems Yes, according to below code, though. CREATE TABLE TBL ( R1 VARCHAR2(15), R2 NVARCHAR2(15), R3 NVARCHAR2(15) ); INSERT INTO TBL(R1, R2) VALUES…
Soon
  • 491
  • 3
  • 16
1
vote
1 answer

PostgreSQL to_char ignores locale

I have PgSQL 9.6 on Fedora 29. System and PgSQL is running with cs_CZ.UTF-8 locale. But when I use to_char(date, 'Day Dy') I get english day name. #shell> echo $LANG cs_CZ.utf8 #sql> show lc_time; cs_CZ.UTF-8 #sql> select to_char(now()::date, 'Day…
Martin Edlman
  • 665
  • 7
  • 15
1
vote
2 answers

Function to return N significant digits of given number

I have seen several questions on this topic, but the answers do not seem useful to me. I must create a function in PostgreSQL 10.5 that returns a number with N significant digits. I have tried different solutions for this problem, however I have a…
Dagon
  • 145
  • 2
  • 12
1
vote
1 answer

Oracle SQL: Replace numbers with variable-length sequences of asterisks

I have a table with employee names and their wages. Now I want to concatenate them in a column. So far so good. But instead of the normal number (ex. $1200) it needs to show me in the query a star (*) for every $100. The result should look like…
PzFPadu
  • 13
  • 3
1
vote
1 answer

TO_CHAR PostgreSQL

I have this query: SELECT hora FROM table; I got "09:45:00" Now, I'm trying to get the hour from that timestamp, and I'm trying: SELECT TO_CHAR(hora,'HH24'), hora FROM table; I was expecting: 9 | 09:45:00 But I got this error: Undefined function:…
pmiranda
  • 7,602
  • 14
  • 72
  • 155
1
vote
1 answer

indexing using to_char(timestamp,'YYYY-MM-DD')) function

I have the following in my SQL where clause. This is running against an Oracle database. The timestamp field is defined in the database as a timestimp field. my select query is : create index ind_timestamp on…
Dach Ch
  • 23
  • 1
  • 8
1
vote
2 answers

How to format number to varchar with thousand separator? Like oracle to_char()

Who knows how in Firebird 3 format number to varchar with thousand separator? Like oracle to_char () select to_char(1234325234234.55,'999G999G999G999G999D00' ) nn from dual -- 1 234 325 234 234,55
Good Bit
  • 23
  • 6
1
vote
2 answers

Oracle SQL TO_CHAR Function return values

I don't understand why these two code results are identically the same? I thought if I have quotes it just concatenate strings. Why not first one is 300.5100? I know second one is 400.5 anyway. Thank you Select to_char('300.5' + '100') From…
Sami
  • 185
  • 1
  • 7
1
vote
3 answers

Oracle to SQL Server: Month + Year where clause

I have an Oracle Query that goes as follow: SELECT ... FROM ... WHERE ... AND to_char(a.bill_Date, 'MMYYYY') = '072017' ... ORDER BY ... In SQL Server, we have the MONTH() and YEAR() functions. SELECT ... FROM ... WHERE ... AND…
DoomerDGR8
  • 4,840
  • 6
  • 43
  • 91
1 2 3
…
14 15