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:

194 questions
50
votes
1 answer

What is the best way to escape non-format characters in Oracle's to_char?

I am trying to print a date in a select statement, but I need to add a letter to the output: to_char(date_updated, 'YYYY-MM-DDTHH:mm:ss') Oracle does not like the T. I just want the T to be output like the colons and the dashes. Can I escape it…
Sixty4Bit
  • 11,052
  • 13
  • 43
  • 58
15
votes
1 answer

Oracle number and varchar join

I have a query that joins two tables. One table has a column that is of type varchar, and the other table has type of number. I have executed my query on 3 oracle databases, and am seeing some strange results I hope can be explained. On two of…
broschb
  • 4,928
  • 4
  • 33
  • 52
8
votes
2 answers

ORACLE convert number to string

Need some help in converting numbers: select to_char(a, '99D99') , to_char(a, '90D99') from ( select 50 a from dual union select 50.57 from dual union select 5.57 from dual union select 0.35 from dual union select 0.4 from dual Will result…
BeHunter
  • 123
  • 1
  • 2
  • 10
8
votes
5 answers

How can I format a number as xxx-xx-xxxx?

I am querying social security number data from a stored procedure and I would like to format it as a social security number in my stored procedure. How can I format xxxxxxxxx like xxx-xx-xxxx in Oracle?
robbie
6
votes
1 answer

Oracle to_char bug

I don't know if it's a bug or what, but when I try to format the Day of the week in a certain way with the to_char function in Oracle, SQL Plus give me this error : ORA-01821: date format not recognized Here's the line that cause a problem SELECT…
Joel
  • 309
  • 2
  • 9
6
votes
2 answers

Why Does Oracle 10g to_char(date time) Truncate Strings?

I got a bug report where Oracle 10g was truncating return values from to_char(datetime): SQL> select to_char(systimestamp, '"day:"DD"hello"') from dual; TO_CHAR(SYSTIMESTAMP,'"DAY:"DD"HE --------------------------------- day:27hel Notably, this…
theory
  • 8,210
  • 8
  • 50
  • 115
5
votes
3 answers

SQL to_char Currency Formatting

I am facing a to_char() currency formatting problem here. The below code is working for me: SELECT TO_CHAR(10000,'L99G999D99MI', 'NLS_NUMERIC_CHARACTERS = ''.,'' NLS_CURRENCY = $') "Amount" FROM DUAL; which will…
Law
  • 199
  • 2
  • 3
  • 12
4
votes
1 answer

PostgreSQL TO_CHAR() Remove Padding on Month Field

Hopefully and easy adjustment. I've googled and not found anything on this. I'm using the DBvis Client to run queries against a Postgres database table. I've used the to_char() function quite a bit with Oracle and PostgreSQL queries, but guess…
John Cowan
  • 986
  • 2
  • 14
  • 27
4
votes
4 answers

Why is Oracle's DECODE giving me a different value than NVL?

This query: select nvl(0.75,0) from dual gives me 0.75 (numeric) but this query: select decode(1,0,null,0.75) from dual gives me '.75' (string). Why? I tried to fix this by changing the second query to: select…
goe
  • 333
  • 1
  • 3
  • 13
4
votes
1 answer

Appending '0' after decimal in Oracle

I want to append trailing '0' to number while performing Select query: what I want is to select 344.89 as 344.890 123213.45 as 123213.450 1.2 as 1.200 I tried using to_char(col_name,'000000.000') but that resulted in 344.89 =>…
manish
  • 1,792
  • 2
  • 22
  • 34
3
votes
3 answers

Oracle Time Comparisons

Is Oracle (10g) doing a proper TIME comparison here, or do I need to convert to decimal time and then compare? E.g., IF (SELECT TO_CHAR(sysdate,'HH24:MI:SS') from dual) <= '15:00' THEN (...) Thanks.
vincent
  • 1,147
  • 1
  • 11
  • 15
3
votes
1 answer

Oracle To_Char function How to handle if it's already a string

Scenario: I am calling a function that returns a field that the user enters in. The field usually returns a number like '120000' which I then use to_char to convert into '120,000'. Problem: Some users enter in values such as '120,000' which gives me…
Gage
  • 7,067
  • 9
  • 44
  • 77
3
votes
2 answers

Postgresql decimal point using to_char

I want make a select that adds decimal point into integers, but when I do it, it shows me bunch of # instead of those numbers. SELECT to_char(1234, '99.99'); What I expected was table with a value of 12.34 but I got ##.## in my select. However, if…
Marvin158
  • 33
  • 4
3
votes
5 answers

Select * from Table and still perform some function on a single named column

I'd like to be able to return all columns in a table or in the resulting table of a join and still be able to transform a date to a string by name. For example Select ID, DESCRIPTION, TO_CHAR(CHANGE_DATE,'YYYY-MM-DD HH24:MI:SS') AS FORMATED_DATE…
Marc
  • 395
  • 3
  • 15
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
1
2 3
…
12 13