General discussion

Locked

SQL 7: How to get formatted data

By Bob Sellman ·
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.

This conversation is currently closed to new comments.

11 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

SQL 7: How to get formatted data

by sl-campbell In reply to SQL 7: How to get formatt ...

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

Collapse -

SQL 7: How to get formatted data

by sl-campbell In reply to SQL 7: How to get formatt ...

Make sure you use SQL pass through or the functions may be processed locally.

Collapse -

SQL 7: How to get formatted data

by sl-campbell In reply to SQL 7: How to get formatt ...

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"

Collapse -

SQL 7: How to get formatted data

by Bob Sellman In reply to SQL 7: How to get formatt ...

Poster rated this answer

Collapse -

SQL 7: How to get formatted data

by Bob Sellman In reply to SQL 7: How to get formatt ...

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.

Collapse -

SQL 7: How to get formatted data

by sl-campbell In reply to SQL 7: How to get formatt ...

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).

Collapse -

SQL 7: How to get formatted data

by sl-campbell In reply to SQL 7: How to get formatt ...

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.

Collapse -

SQL 7: How to get formatted data

by sl-campbell In reply to SQL 7: How to get formatt ...

Why did I answered this twice? Hit the wrong button I guess...

Collapse -

SQL 7: How to get formatted data

by sl-campbell In reply to SQL 7: How to get formatt ...

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*.,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

Collapse -

SQL 7: How to get formatted data

by Bob Sellman In reply to SQL 7: How to get formatt ...

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.

Back to Web Development Forum
11 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums