Format numbers with commas

Hello

Nice simple T-SQL question that will no doubt prove totally uncontroversial :)

Anyone know the easiest way to get from:
123456789
to
123,456,789
in T-SQL?

Currently I cast my int as money, use convert to varchar with style 1 and then lop off the .00. Is there an easier way?

Two things before you get all upset and start squealing about front ends:
1) These are admin functions that I am running from SSMS. I don't want to go to the bother of creating an application when SSMS is perfectly adequate for what I need (namely to-a-large-degree unformatted result sets).
2) I'm not too fussed about it just curious.

Ta & tra la!good old style 1, that seems like the best approach

i did a quick search and came up with a number of threads where this question was asked and the answer was a resounding "it's a display issue and you should do that with the front end application, not sql"

of course, you already said you don't have an app...|||Thanks Rude-boy - I got the "do it front end" stuff too. :)

Just wondered if there was something less verbose that I just don't know of. Even an SSMS setting would be fine.

Format Dates in ISO 8601

Hi,

I'm totally new at SQL Server, I'm sorry if this question looks very simple, but I just don't know how to change the datetime value in a field on my database.

I'm working in vb.net and I have a datetime field in sql server, I can store date values passing this value:

DatePick.Value.ToString("s")

dates are saved fine, but if want to make this query:

Select * from backlog where date1>=isdate('21/02/2006') order by date1 asc

I get all the values from that field, no filter is done. Date are shown like this 2004-05-23 14:25:10.000 in the query.

What do I have to do ? do I have to change the date format of my field to ISO 8601 ? how ? do I use a script or what ?

Please remeber that my sql script should be done from vb.

I really appreciate your help.

George

Hi,

ok, the following query looks strange to me as the "isdate('21/02/2006')" part will always return 1 as the date in the brackets is always a date, the function isdate only returns 0 for a non.date and 1 for a valid date. For a date comparison you should use something like date1>= '02/21/2006' (see the switched month and day for an automatic / implicit date conversion)

I get all the values from that field, no filter is done.

Date are shown like this 2004-05-23 14:25:10.000 in the query.

Thats by design, datetime values include date as well as time, but the application should be able to get the value and only use the datepart of it (if you want to).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

You don't necessarily have to switch the day and month around. This is actually controlled by your language setting (for the login). If you have a language setting that interprets dates as day/month/year, then you can use your original format.

When using dates within SQL Server, it is best to use the ISO Unseperated Date Format which is YYYYMMDD. SQL Server will never misinterpret a date in this format.

The problem with your query is that IsDate returns a 1 or 0. Since IsDate('21/02/2006') probably returns a 1, you are actually selecting all dates greater than Jan 2, 1900 (which is probably all of your records).

I suggest you modify your query to...

Select * from backlog where date1 >= '20060221' order by date1 asc

To determine your language settings, you can run:

sp_configure 'default language'

This will return a config_value which you can use to lookup the actual language when you run:

sp_helplanguage

You should know that the language setting is based on the login.