Convert Access query to SQL

Question

I am converting Access query to SQL Server.

I want to convert below lines to SQL

1. Format (210.6, "Standard")
2. Format (210.6, "#,##0.00")

How do i convert it to SQL query.

I have tried with below, but still not able to find the solution.

For the first query, i found below solution, which is correct

    1. CONVERT(varchar, CAST(tSRO.OutputF11 AS money), 1)

Now, for second query, i do not know what i have to do.

Result for first query should be Result: '210.60' and for second query Result: '210.60' — User5590
– User5590, Commented Oct 24, 2015 at 12:56

Lukasz Szozda · Accepted Answer · 2015-10-24 13:20:31Z

2

From SQL Server 2012+ you can use FORMAT:

SELECT FORMAT(210.6, '#,##0.00')  -- 210.60

SELECT FORMAT(1210.6, '#,##0.00')  -- 1,210.60

LiveDemo

SQL Server before 2012:

SELECT REPLACE(CONVERT(VARCHAR,CONVERT(MONEY, 1210.6),1),'.00','')  -- 1,210.60

LiveDemo2

Warning:

This operation is pure for presentation layer and should be done in application.

edited Oct 24, 2015 at 13:20

answered Oct 24, 2015 at 12:59

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

User5590 Over a year ago

I have tried it in my environment and it is lower version. so that's why it is not working

User5590 Over a year ago

what if i have a lower version?

User5590 Over a year ago

Thanks for your help.

Dan Guzman Over a year ago

Although this solution will work, formatting data for display purposes is better done in the presentation layer rather than SQL. This practice improves database performance and leverages front-end formatting capabilities that are generally more robust.

Dan Guzman Over a year ago

@lad2025, It doesn't matter of the RDMBS provides formatting capabilities or not. I'm just suggesting the best practice is presentation layer formatting. You are right that it depends but the only overarching reason to do formatting in SQL is initial porting of a legacy application. That may apply here but I just want to caution it's not proper development practice for new development.

|

Collectives™ on Stack Overflow

Convert Access query to SQL

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related