Newsgroup: it.comp.lang.delphi
Titolo: Formattazione di numeri in SQL

Come si fa a formattare il risultato di un'espressione in SQL?
Ad esempio se volete calcolare il valore in euro di L. 10.000 come fate per
le cifre decimali?
Cosa darei per avere un manuale serio delle funzioni supportate da SQL del
BDE ...
-- 
Ciao da Giuseppe Fraddosio
    // FidoNET 2:335/727.2    
 \X/  
fradd...@libero.it
 
fradd...@tin.it
 
Membro AGI Puglia Sez. Bari 
Powered By Amiga2000T/060@50Mhz/CV3D 4Mb/AmigaOS 3.5

 le cifre decimali?
credo di non sbagliare dicendoti che in SQL non esistono formattazioni,
puoi usare la prop del TField relativa alla maschera di visualizzazione.

Hello Carlo

Non penso. Con InfoMaker ho visto che c'e' un NumberFormat e volevo vedere
se BDE lo supporta ma non sono riuscito a capirlo.


 se BDE lo supporta ma non sono riuscito a capirlo.
non so cosa sia InfoMaker, comunque parlavo di SQL standard...
p.s.
Nel BDE qualcosa che si chiami NumberFormat non esiste.

Ciao
Esistono due funzioni di SQL7 per il typecasting
-CAST
-CONVERT
Segue una copia dell'help
Ciao :-)
Mimmo
-------------------------------------ESTRATTO DA SQL Books On Line
CAST and CONVERT (T-SQL)
Explicitly converts an expression of one data type to another. CAST and
CONVERT provide similar functionality.
Syntax
Using CAST:
CAST(expression AS data_type)
Using CONVERT:
CONVERT (data_type[(length)], expression [, style])
Arguments
  expression
  Is any valid Microsoft® SQL ServerT expression. For more information, see
Expressions.
  data_type
  Is the target system-supplied data type. User-defined data types cannot be
used. For more information about available data types, see Data Types.
  length
  Is an optional parameter of nchar, nvarchar, char, varchar, binary, or
varbinary data types.
  style
  Is the style of date format you want when converting datetime or
smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar,
or nvarchar data types), or the string format when converting float, real,
money, or smallmoney data to character data (nchar, nvarchar, char, varchar,
nchar, or nvarchar data types).
  In the table, the two columns on the left represent the style values for
datetime or smalldatetime conversion to character data. Add 100 to a style
value to get a four-place year that includes the century (yyyy).
        Without
        century
        (yy) With
        century
        (yyyy)
        Standard
        Input/Output**
        - 0 or 100 (*)  Default mon dd yyyy hh:miAM (or PM)
        1 101 USA mm/dd/yy
        2 102 ANSI yy.mm.dd
        3 103 British/French dd/mm/yy
        4 104 German dd.mm.yy
        5 105 Italian dd-mm-yy
        6 106 - dd mon yy
        7 107 - mon dd, yy
        8 108 - hh:mm:ss
        - 9 or 109 (*)  Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM
(or PM)
        10 110 USA mm-dd-yy
        11 111 JAPAN yy/mm/dd
        12 112 ISO yymmdd
        - 13 or 113 (*)  Europe default + milliseconds dd mon yyyy
hh:mm:ss:mmm(24h)
        14 114 - hh:mi:ss:mmm(24h)
        - 20 or 120 (*)  ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
        - 21 or 121 (*)  ODBC canonical (with milliseconds) yyyy-mm-dd
hh:mi:ss.mmm(24h)
        *    The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or
120, and 21 or 121) always return the century (yyyy).
        ** Input when converting to datetime; Output when converting to
character data.
----------------------------------------------------------------------------
--
  Important By default, SQL Server interprets two-digit years based on a
cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049
and the two-digit year 50 is interpreted as 1950. Many client applications,
such as those based on OLE Automation objects, use a cutoff year of 2030.
SQL Server provides a configuration option (two digit year cutoff) that
changes the cutoff year used by SQL Server and allows the consistent
treatment of dates. The safest course, however, is to specify four-digit
years.
----------------------------------------------------------------------------
--
  When you convert to character data from smalldatetime, the styles that
include seconds or milliseconds show zeros in these positions. You can
truncate unwanted date parts when converting from datetime or smalldatetime
values by using an appropriate char or varchar data type length.
  This table shows the style values for float or real conversion to
character data.
        Value Output
        0 (the default) 6 digits maximum. Use in scientific notation, when
appropriate.
        1 Always 8 digits. Always use in scientific notation.
        2 Always 16 digits. Always use in scientific notation.
  In the following table, the column on the left represents the style value
for money or smallmoney conversion to character data.
        Value Output
        0 (the default) No commas every three digits to the left of the
decimal point, and two digits to the right of the decimal point; for
example, 4235.98.
        1 Commas every three digits to the left of the decimal point, and
two digits to the right of the decimal point; for example, 3,510.92.
        2 No commas every three digits to the left of the decimal point, and
four digits to the right of the decimal point; for example, 4235.9819.
Return Types
Returns the same value as data type 0.
Remarks
Implicit conversions are those conversions that occur without specifying
either the CAST or CONVERT function. Explicit conversions are those
conversions that require the CAST (CONVERT) function to be specified. This
chart shows all explicit and implicit data type conversions allowed for SQL
Server system-supplied data types.
----------------------------------------------------------------------------
----
Note Because Unicode data always use an even number of bytes, use caution
when converting binary or varbinary to or from Unicode supported data types.
For example, this conversion does not return a hexadecimal value of 41, but
of 4100:
SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary)
----------------------------------------------------------------------------
----
Automatic data type conversion is not supported for the text and image data
types. You can explicitly convert text data to character data, and image
data to binary or varbinary, but the maximum length you can specify is 8000.
If you attempt a conversion that is not possible (for example, if you
convert a character expression that includes letters to an int), SQL Server
generates an error message.
When converting character or binary expressions (char, nchar, nvarchar,
varchar, binary, or varbinary) to an expression of a different data type,
data can be truncated, only partially displayed, or an error is returned
because the result is too short to display. Conversions to char, varchar,
nchar, nvarchar, binary, and varbinary are truncated, except for the
conversions shown in this table.
      From data type To data type Result
      int, smallint, or tinyint char *
        varchar *
        nchar E
        nvarchar E
      money, smallmoney, numeric, decimal, float, or real
      char
      E
        varchar E
        nchar E
        nvarchar E
      * Result length too short to display.
      E Error returned because result length is too short to display.
Microsoft SQL Server guarantees that only roundtrip conversions, conversions
that convert a data type from its original data type and back again, will
yield the same values from release to release. This example shows such a
roundtrip conversion:
DECLARE @myval decimal (5, 2)
SET @myval = 193.57
SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))
-- Or, using CONVERT
SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))
Do not attempt to construct, for example, binary values and convert them to
a data type of the numeric data type category. SQL Server does not guarantee
that the result of a decimal or numeric data type conversion to binary will
be the same between releases of SQL Server.
When data types are converted with a different number of decimal places, the
value is truncated to the most precise digit. For example, the result of
SELECT CAST(10.6496 AS int) is 10.
This example shows a resulting expression that is too small to display.
USE pubs
SELECT SUBSTRING(title, 1, 25) AS Title, CAST(ytd_sales AS char(2))
FROM titles
WHERE type = 'trad_cook'
Here is the result set:
Title
------------------------- --
Onions, Leeks, and Garlic *
Fifty Years in Buckingham *
Sushi, Anyone?            *
(3 row(s) affected)
When data types in which the target data type has fewer decimal points than
the source data type are converted, the value is rounded. For example, the
result of CAST(10.3496 AS money) is $10.35.
SQL Server returns an error message when char, nchar, varchar, or nvarchar
data is converted to int, float, numeric, or decimal; or when an empty
string (" ") is converted to int or float.
Using Binary String Data
When binary or varbinary data is converted to character data and an odd
number of values is specified following the x, SQL Server adds a 0 (zero)
after the x to make an even number of values.
Binary data consists of the characters 0 through 9 and A through F (or
through f), in groups of two characters each. Binary strings must be
preceded by 0x. For example, to input FF, type 0xFF. The maximum value is a
binary value of 8000 bytes, each of which is FF. The binary data types are
not for hexadecimal data, but rather for bit patterns. Conversions and
calculations of hexadecimal numbers stored as binary data can be unreliable.
When specifying the length of a binary data type, every two characters count
as one. A length of 10 signifies that 10 two-character groupings will be
entered.
Empty binary strings, represented by 0x, can be stored as binary data.
Examples
A. Use both CAST and CONVERT
Each example retrieves the titles for those books that have a 3 in the first
digit of year-to-date sales, and converts their ytd_sales to char(20).
-- Use CAST.
USE pubs
GO
SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CAST(ytd_sales AS char(20)) LIKE '3%'
GO
-- Use CONVERT.
USE pubs
GO
SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CONVERT(char(20), ytd_sales) LIKE '3%'
GO
Here is the result set for either query:
Title                          ytd_sales
------------------------------ -----------
Cooking with Computers: Surrep 3876
Computer Phobic AND Non-Phobic 375
Emotional Security: A New Algo 3336
Onions, Leeks, and Garlic: Coo 375
(4 row(s) affected)
B. Use CAST with arithmetic operators
This example calculates a single column computation (Copies) by dividing the
total year-to-date sales (ytd_sales) divided by the individual book price
(price). This result is converted to an int data type after being rounded to
the nearest whole number.
USE pubs
GO
SELECT CAST(ROUND(ytd_sales/price, 0) AS int) AS 'Copies'
FROM titles
GO
Here is the result set:
Copies
-----------
205
324
6262
205
102
7440
NULL
383
205
NULL
17
187
16
204
418
18
1263
273
(18 row(s) affected)
C. Use CAST to concatenate
This example concatenates noncharacter, nonbinary expressions using the CAST
data type conversion function.
USE pubs
GO
SELECT 'The price is ' + CAST(price AS varchar(12))
FROM titles
WHERE price > 10.00
GO
Here is the result set:
-------------------------
The price is 19.99
The price is 11.95
The price is 19.99
The price is 19.99
The price is 22.95
The price is 20.00
The price is 21.59
The price is 10.95
The price is 19.99
The price is 20.95
The price is 11.95
The price is 14.99
(12 row(s) affected)
D. Use CAST for more readable text
This example uses CAST in the select list to convert the title column to a
char(50) column so the results are more readable.
USE pubs
GO
SELECT CAST(title AS char(50)), ytd_sales
FROM titles
WHERE type = 'trad_cook'
GO
Here is the result set:
                                                       ytd_sales
--------------------------------------------------     ---------
Onions, Leeks, and Garlic: Cooking Secrets of the      375
Fifty Years in Buckingham Palace Kitchens              15096
Sushi, Anyone?                                         4095
(3 row(s) affected)
E. Use CAST with LIKE clause
This example converts an int column (the ytd_sales column) to a char(20)
column so that it can be used with the LIKE clause.
USE pubs
GO
SELECT title, ytd_sales
FROM titles
WHERE CAST(ytd_sales AS char(20)) LIKE '15%'
    AND type = 'trad_cook'
GO
Here is the result set:
title                                                        ytd_sales
------------------------------------------------------------ -----------
Fifty Years in Buckingham Palace Kitchens                    15096
(1 row(s) affected)
(c) 1988-98 Microsoft Corporation. All Rights Reserved