Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gshacte
Starting Member
17 Posts |
 Posted - 2010-02-25 : 20:57:25
|
| I would like one column in a query to have two decimal places. I am trying to use the 'column' keyword, but I keep getting notified that there is incorrect syntax near the keyword 'column'. All I want to do is specify the column number, give the column a name, and have it display with two decimal places.Gerry |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-02-25 : 21:00:23
|
convert(decimal(10,2), column_name)
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
gshacte
Starting Member
17 Posts |
Posted - 2010-02-26 : 08:07:36
|
| The column I want to modify is:(DATEDIFF(minute, sheet.stime, sheet.etime)/60.0) as minutes, (To make it work, I needed the parenthesis around the whold statement) before the 'as minutes'. I want the results in fractions of an hour, so I divide by 60.0). Where in the query would I put:convert(decimal(10,2), minutes)No matter where I put it, I seem to get an error. If I put the convert right after the DATEDIFF, I get: Msg 102, Level 15, State 1, Line 11Incorrect syntax near '.'I think it does not belong as part of the select statement here because there is another column below it being selected. If I make it the last statement in the select, I get 'invalid keyword 'convert'. I tried using the following approach in the select statement:convert(decimal(10,2), minutes)(DATEDIFF(minute, sheet.stime, sheet.etime)/60.0),but get:Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'convert'.Gerry |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-26 : 08:12:24
|
quote:
Originally posted by gshacte
I would like one column in a query to have two decimal places. I am trying to use the 'column' keyword, but I keep getting notified that there is incorrect syntax near the keyword 'column'. All I want to do is specify the column number, give the column a name, and have it display with two decimal placesGerry
do you mean you need to specify an explicit ordinal number?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
gshacte
Starting Member
17 Posts |
Posted - 2010-02-26 : 14:10:59
|
| Thanks. For example, I am currently displaying the column as 8.500000. I would to display it as 8.5 on the output. Gerry |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-02-26 : 14:23:22
|
| Do u mean the value 8.500000 what if the value is 8.49PBUH |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-02-26 : 14:31:56
|
| Maybe this?CONVERT(decimal(10,2),DATEDIFF(minute, sheet.stime, sheet.etime)/60.0) |
|
|
|
gshacte
Starting Member
17 Posts |
Posted - 2010-02-26 : 17:14:53
|
| Thank you so much again for your help, in introducing me to 'convert' and applying it properly to the object I wanted converted. It seems to work as expected.Gerry |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-27 : 00:32:06
|
| "(DATEDIFF(minute, sheet.stime, sheet.etime)/60.0) as minutes,"Pedantic point, but that would be hours, rather than minutes, wouldn't it? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-27 : 00:34:31
|
quote:
Originally posted by Kristen
"(DATEDIFF(minute, sheet.stime, sheet.etime)/60.0) as minutes,"Pedantic point, but that would be [red]hours[/red, rather than minutes, wouldn't it?
yeah that should be in hours seems like OP missed it  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
column formatting