OraFAQ Forum: SQL & PL/SQL » Format Dollar and Date Fields

Home » SQL & PL/SQL » SQL & PL/SQL » Format Dollar and Date Fields

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Format Dollar and Date Fields [message #687599]

Wed, 12 April 2023 09:31

_rachel_
Messages: 1
Registered: April 2023

Junior Member

Hello!

I created a query using Noetix/Oracle and I have the report set to create a report nightly. The output is really ugly and I'm hoping there's a way to edit this portion of the code so each of the dates are in MM/DD/YYYY format and the dollar amounts are in $X,XXX.XX format?

SELECT DISTINCT
"AP_Invoice_Payments"."Vendor_name_alt" AS "PAYEE ID",
"AP_Checks"."Vendor_Name" AS "CLIENT NAME",
"AP_Checks"."Payment_Document_Name" AS "PAYMENT TYPE",
"AP_Checks"."Check_Number" AS "PAYMENT NUMBER",
"AP_Checks"."Check_Date" AS "PAYMENT DATE",
"AP_Checks"."Amount" AS "AMOUNT",
"AP_Checks"."Status_Lookup_Code" AS "STATUS",
"AP_Checks"."Cleared_Date" AS "CLEARED DATE",
"AP_Checks"."Void_Date" AS "VOID DATE"

This is just a portion of the code where the column headers are renamed, but is there something I can add in addition to this for number formatting?

Report message to a moderator

Re: Format Dollar and Date Fields [message #687601 is a reply to message #687599]

Wed, 12 April 2023 12:32

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Take a look at the TO_CHAR function in the SQL Reference manual.

I leave the rest as an exercise for the student.

Report message to a moderator

Re: Format Dollar and Date Fields [message #687602 is a reply to message #687599]

Wed, 12 April 2023 13:12

Barbara Boehmer
Messages: 9063
Registered: November 2002
Location: California, USA

Senior Member

There are multiple ways to do this.

If you have something like this and your date column is of date datatype and your amount column is of number datatype:

C##SCOTT@XE_21.3.0.0.0> SELECT DISTINCT
  2  	    "EMP"."HIREDATE"				AS "PAYMENT DATE",
  3  	    "EMP"."EMPNO" + NVL(("EMP"."COMM"/10000),0) AS "AMOUNT"
  4  FROM   "EMP"
  5  /

PAYMENT D     AMOUNT                                                            
--------- ----------                                                            
17-DEC-80       7369                                                            
20-FEB-81    7499.03                                                            
22-FEB-81    7521.05                                                            
02-APR-81       7566                                                            
28-SEP-81    7654.14                                                            
01-MAY-81       7698                                                            
09-JUN-81       7782                                                            
09-DEC-82       7788                                                            
17-NOV-81       7839                                                            
08-SEP-81       7844                                                            
12-JAN-83       7876                                                            
03-DEC-81       7900                                                            
03-DEC-81       7902                                                            
23-JAN-82       7934                                                            

14 rows selected.

Then you can use TO_CHAR on each such column to specify how you want them displayed as below.

C##SCOTT@XE_21.3.0.0.0> SELECT DISTINCT
  2  	    TO_CHAR ("EMP"."HIREDATE", 'MM/DD/YYYY')			       AS "PAYMENT DATE",
  3  	    TO_CHAR ("EMP"."EMPNO" + NVL(("EMP"."COMM"/10000),0), '$9,999.99') AS "AMOUNT"
  4  FROM   "EMP"
  5  /

PAYMENT DA AMOUNT                                                               
---------- ----------                                                           
12/17/1980  $7,369.00                                                           
02/20/1981  $7,499.03                                                           
02/22/1981  $7,521.05                                                           
04/02/1981  $7,566.00                                                           
09/28/1981  $7,654.14                                                           
05/01/1981  $7,698.00                                                           
06/09/1981  $7,782.00                                                           
12/09/1982  $7,788.00                                                           
11/17/1981  $7,839.00                                                           
09/08/1981  $7,844.00                                                           
01/12/1983  $7,876.00                                                           
12/03/1981  $7,900.00                                                           
12/03/1981  $7,902.00                                                           
01/23/1982  $7,934.00                                                           

14 rows selected.

Or you can use the following command to change the default date format for the session, so you don't need to use TO_CHAR.

C##SCOTT@XE_21.3.0.0.0> ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY'
  2  /

Session altered.

In SQL*Plus you can also change the numeric format with the following command, but you may not be able to use that in your environment:

C##SCOTT@XE_21.3.0.0.0> SET NUMFORMAT '$9,999.99'

Then you can use your original code to get what you want without TO_CHAR, as shown below, but it will affect all queries in the session.

C##SCOTT@XE_21.3.0.0.0> SELECT DISTINCT
  2  	    "EMP"."HIREDATE"				AS "PAYMENT DATE",
  3  	    "EMP"."EMPNO" + NVL(("EMP"."COMM"/10000),0) AS "AMOUNT"
  4  FROM   "EMP"
  5  /

PAYMENT DA     AMOUNT                                                           
---------- ----------                                                           
12/17/1980  $7,369.00                                                           
02/20/1981  $7,499.03                                                           
02/22/1981  $7,521.05                                                           
04/02/1981  $7,566.00                                                           
09/28/1981  $7,654.14                                                           
05/01/1981  $7,698.00                                                           
06/09/1981  $7,782.00                                                           
12/09/1982  $7,788.00                                                           
11/17/1981  $7,839.00                                                           
09/08/1981  $7,844.00                                                           
01/12/1983  $7,876.00                                                           
12/03/1981  $7,900.00                                                           
12/03/1981  $7,902.00                                                           
01/23/1982  $7,934.00                                                           

14 rows selected.

Report message to a moderator

Re: Format Dollar and Date Fields [message #687604 is a reply to message #687602]

Wed, 12 April 2023 13:24

mathguy
Messages: 79
Registered: January 2023

Member

Ideally such formatting should be done in the "reporting tool" - whatever additional software you are using to query the database and to generate the report. That can be SQL*Plus (very common), SQL Developer, Toad, etc. - or a more complex machinery like ApEx. Doing the formatting in the query itself is generally NOT the best practice. If you do the formatting in the query itself, this means that the output will have strings in columns that are expected to have DATE data type, resp. NUMBER data type.

We can help you with the "better" approach, but you need to tell us what "reporting tool" you are using. Barbara already hinted to how to do some of what you need in SQL*Plus. (Note though that at that point, the question is no longer an SQL question, but instead it is a question about the specific reporting tool!)

Report message to a moderator

Re: Format Dollar and Date Fields [message #687607 is a reply to message #687604]

Thu, 13 April 2023 04:42

Littlefoot
Messages: 21783
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

mathguy

That can be SQL*Plus (very common)

I believe you meant to say "very UNcommon". SQL*Plus was common 20 years ago, but today most developers I know use some GUI tool. More clicking and guessing, less understanding what you're actually doing, but that's how it goes.

But yes, I agree with what you said about formatting in reporting tools.

Just to illustrate it to Rachel, this is a common query which isn't anything special - selecting some rows from a table, sorting them on DATE column:

SQL> select hiredate,
  2         sal
  3  from emp
  4  where deptno = 20
  5  order by hiredate;

HIREDATE        SAL
-------- ----------
17.12.80        840
02.04.81       2975
03.12.81       3000
09.12.82       3000
12.01.83       1100

It is easy to find e.g. first hire date and sum of all salaries:

SQL> select min(hiredate) min_date,
  2         sum(sal) sum_sal
  3  from emp
  4  where deptno = 20;

MIN_DATE    SUM_SAL
-------- ----------
17.12.80      10915

Though, it looks kind of ugly and - as you're frequently working with the same data - you decide to format date and salary and create a view, thinking that you'll rather use that view in the future instead of typing the same query all over again:

SQL> create or replace view v_emp_20 as
  2  select to_char(hiredate, 'dd.mm.yyyy') hiredate,
  3         to_char(sal, '$999G990D00') sal
  4  from emp
  5  where deptno = 20;

View created.

OK, so let's select the same data as before, using that view:

SQL> select min(hiredate) min_date,
  2         sum(sal) sum_sal
  3  from v_emp_20;
       sum(sal) sum_sal
           *
ERROR at line 2:
ORA-01722: invalid number

Whoops! It doesn't work any more! Why? Because you're now trying to sum strings, not numbers!

How about sorting data on the same hiredate column as earlier:

SQL> select hiredate,
  2         sal
  3  from v_emp_20
  4  order by hiredate;

HIREDATE   SAL
---------- ------------
02.04.1981    $2.975,00
03.12.1981    $3.000,00
09.12.1982    $3.000,00
12.01.1983    $1.100,00
17.12.1980      $840,00

Nah, that's wrong again. Actually, it is correct, strings are sorted correctly, but - you'd want data to be sorted on their DATE representation.

Therefore, yes - leave formatting to reporting tools.

Report message to a moderator

Previous Topic:	sql query
Next Topic:	SQL Query help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Oct 02 06:33:43 CDT 2023