Format in SQL

[samsmithnz]

Whats the VB.NET Format/Tostring equivalent in SQL? I can't find it, but I'm sure I've used it before...

[Nerseus]

In SQL Server? I use CONVERT. For example, to convert a date to US standard:

SELECT CONVERT(varchar, [datecolumnname], 101) [newDateColumnName] FROM...

 

You can also use CAST, I believe.

 

Formatting is limited, but usually gets you what you want.

 

-ner

[samsmithnz]

I was thinking more about taking a number 3000 and returning it as a string with commas like: "3,000.00". In .NEt I could do it by doing:

 

(3000).ToString("#,##0.00")

[rjonas]

Hi,

 

You can convert this to a type of Money, and then to a Varchar using a style of 1.

 

select convert(varchar(50),convert(money,fieldname),1) from...

 

The style of 1 gives 2 digits after the decimal point and inserts a comma between every 3 digits before the decimal point.

 

There is a more detailed description of all the different styles

here.

 

Regards

Richard