Web Development

Ask something
Discussion Locked

SQL 7: How to get formatted data

0 helpful
11 replies

by bob sellman

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Is there a way in a SQL 7 query to have the data come back formatted?

I would like to duplicate the functions avaialable with the FORMAT command which is in Access (97) that can be included in a query (when built using VBA code), so instead of getting, for example, numbers that might have anywhere from one to 6 decimal places displayed, the numbers could all have 2 decimal places (or whatever is needed for that column). I've looked through the help files and some other sources and so far I have been unable to find anything in Transact-SQL that seems to duplicate the formatting capability available in Access.

11 replies

by sl-campbell

Sure! There are many functions that can be used to do this:

Mathematical:
ROUND(price,2) AS "SellPrice"

String:
LEFT(middle_name,1) AS "Middle Initial"

Dates:
DATEPART("mm",selldate) AS "Sell Month" - returns for example July
DATENAME("mm",selldate) AS "Sell Month" - returns for example 7
DATEDIFF("dd", 30, invoicedate) AS "Payment due date"

By the way, column headings can be written as:
Select column_name AS column_heading

or

Select column_heading = column_name

orSelect column_name1, column_heading1,column_name2,column_heading2

by sl-campbell

More info on Arithmetic/Comparison/Logical Operators under these topics in "SQL Server Books Online"
and on Functions in the "Transact-SQL Reference" and "Database Developers Companion" in "SQL Server Books Online"

by bob sellman

The ROUND (and similar functions) works to eliminate the extra decimal places, so it solves "half" of the problem. However, I would like to also control the following:
1. Always show a specific number of decimal places. For example, 24.10 shouldbe displayed, not just 24.1.

2. If possible to do, I'd also like to add, for example, a dollar sign in front of some values (and perhaps a percent sign after some others). This is not as important as #1.

by sl-campbell

1.
Use STR

STR(float_expr [length [decimal]])
Returns character data converted from numeric data
Length is the total length, decimal is the number of spaces to the right of the decimal.

2.
Just add a $ or % and conert the number
SELECT '$' + CONVERT (varchar(10),ISNULL(price, 0.00))

I added ISNULL to show you how to return a default value if the field is NULL.

Looks like alot of work - and I must admit, out-side of using aggregated function with a Group-By, I almost never format WITH-IN an SQL statement - not in VB, Access, or whatever. I only format at the client (results are place in variables or properties and THEN formated/calculated, or I format the grid columns. Seldom do I format a text box. I never use bound controls - (I hate them and consider them not good programming techniques, except when used for small fast things, I do not have total control over what is being done except with alot of work, and consider having 50 forms with many text boxes and having toformat all of them, or add the formatting to all those SQL statements- easier to pass the field to one small function that checks for NULLs, captures the field type, and formats accordingly whether NULL or not.
And it makes it harder to port an application).

by sl-campbell

Of course, that's my "Story".
But anyways, look at the mentioned references for all the available functions, etc, check what they do and the syntax - that way you are aware about WHAT is available, and when you know what tools are at hand, you can decide better which one is appropiate for the job - there are not that many and are easy to learn.

by sl-campbell

This is getting to be a long answer (my fault).

I am going to list below for you some of the possibilties of the CONVERT function:

Syntax: CONVERT (datatype [(length)], expression [,style])

datatype = convert to what (use sp_help to get a complete list):
Character - char[(n)]

Varying Character - varchar[(n)]

Date and time - datetime or smalldatetime - the "style" parameter of the CONVERT is used for this datatype as in mm/dd/yy, dd/mm/yyyy hh:mi:ss, etc.
-0 or 100 returns the default as in mon dd yyyy hh:mi AM (or PM)
-1 returns mm/dd/yy (USA format and no century), 101 would return the same with the century as in mm/dd/yyyy
-8 returns 113 returns the European default and milliseconds as in dd mon yyyy hh:mi:ss:ms(24h)
-2 or 102 returns ANSI format of yy.mm.dd or yyyy.mm.dd

Exact number - decimal[(p[,s])] or numeric[(p[,s])] - where p is the precision and d are the number of decimals (p would therefore be the total number of numbers on the left and right sideof the decimal point, and decimal the number of numbers on the right side)

Monetary - money, smallmoney

Text and image - text, image

SELECT title, price, '20% discount' = CONVERT(decimal(6,2),ROUND((price*.8),2)) From titles
Returns a column with the heading: "'20% discount" and the field is rounded and then formated to have a max of 6 numbers and a max of 2 digits (note: the d value cannot be larger that the p value)

SELECT 'Date Received' = CONVERT(datetime, daterecvd, 101) FROM orders

Hope this helps

by bob sellman

Thanks for your help. I'm looking to use these functions for a grouping query in a generic query builder form I've been using in Access and am rewriting to use with a SQL back end. The result is displayed in spreadsheet like format that can easilybe copied to Excel for more manipulation. Both the ROUND and STR functions help, with STR forcing the number of decimal places. The only "gotcha" with the STR function is that (because they are now strings, not numbers) the display does not right justify the displays, so the decimal points are not lined up. I'll have to decide which is better: two decimal places not lined up or right justified numbers that do not always display two (or whatever) decimal places.

This discussion is closed to new replies.