Formatting/Returning stored proc results as XML

I have a stored proc that contains a simple select statement. The select statement does not use the 'FOR XML' clause, nor can the select statement be modified to use the 'FOR XML' clause (for compatibility reasons)

Is there a way to execute the stored proc such that it returns its output in XML? I'm looking for something similar to this:

Code Snippet

exec spGetUserDetails @.ID = 1234 for xml raw

Thanks.

I found an approach that would preserve backwards compatibility while allowing me to manipulate results retrieved by a stored proc. It has to do with Table-valued user-defined functions:

Move the select statement from the stored proc into a user--defined function that returns TABLE:

Code Snippet

-- Example from Books Online

CREATE FUNCTION fn_SalesByStore (@.storeid int)
RETURNS TABLE
AS
RETURN ( SELECT * from Products where StoedID = @.storeid );

Modify the original stored proc to call this user-defined function, i.e., the stored proc wraps the UDF hence preserving backwards compatibility:

Code Snippet

-- Inside the stored proc comment the select statement and call the UDF

-- SELECT * from Products where StoedID = @.storeid -- No longer needed

select * from fn_SalesByStore (@.storeid);

Now I can call the UDF to retreive the same results that would have been retrieved by the original stroed proc, and more importantly, manipulate them as required.

Code Snippet

select * from fn_SalesByStore (@.storeid) for xml raw

Yazan

Formatting the numerical output

Hi All,

I need to control the output format of a request on "int" columns. For instance, if the "select" returns me a "0" (zero), I would like it to be return with 4 digit = "0000" because this results is then append to some other to built a phone-like number : +33 2121-0000-1544.
In that case, "0" is not equal to "0000"

My ASP request is in attach.

THX.

Fabrice.I found a solution ... Thank's anyway ;)

Formatting Sub Totals in a Matrix

Would some one know how to control the formatting of Matrix Sub totals, such as Border around it, background color and font etc...I can't select the grey textbox that is rendered if sub totals are chosen from the context menu for a group.

Any help will be appreciated.

If you right-click on the green triangle in the corner of the subtotal label and click properties you should be able to access the properties of the subtotal text box (I believe it is actual labeled Subtotal by default).

Hope this helps,

Simone

|||I love this forum....Thanks!!!!!!

Formatting reports in SQL PLUS

I having trouble formatting a report for a particular table. The table is quite large and has 20+ columns. Im trying to do a select * from the table, but the output is really bad. I know it has something to do with wrap around, but im not sure how to fix it. Ive increased the LINESIZE and PAGESIZE which helped, but its still impossible to read. The output looks like this:

APP_ID SSN AREA_CODE TEL_NO QUE QUE QUES_LEAVEREASON APPLY_POSITION START_DAT AMTHOURS_
---- ---- ---- ---- -- -- ------- ----- --- -
HIGH_NAME HIGH_CITY HIGH_STATE COLLEGE_NAME COLLEGE_LOCATION COLLEGE_MAJOR COL
------- ------- ----- ------- -------
EXTRA_ACTIVITIES OFFICES_HELD OTHER_SCHOOLING QUE
-------- -------- -------- --
80000 443264455 561 6548890 no no NULL Greeter 04-APR-04 40 yes yes walk-in
Chase HS Bloomfield IL NULL NULL NULL NULL NULL
NULL NULL NULL yes

80001 887651109 772 7817764 no no NULL Manager 12-DEC-05 40 yes yes friend
Broward County HS Palm Beach FL FAU Boca Raton Management Business NULL
NULL NULL NULL yes

80002 198667112 772 5443234 no no NULL Stocker 08-AUG-05 40 yes yes TV
Centiennal Port St. Lucie FL NULL NULL NULL NULL NULL
NULL NULL NULL yes

80003 332690823 561 7734456 no no NULL Security guard 01-JAN-04 40 yes yes
George OLeary Callamajo MD NULL NULL NULL NULL NULL
NULL NULL NULL yes

80004 557751123 772 2601973 no no NULL Cashier 07-JUL-07 30 yes yes friends
St.Lucie County Port St. Lucie FL FAU Boca Raton Dentistry Business NU
NULL NULL NULL yes

80005 554321123 561 7812238 no no NULL Manager 01-JUN-04 40 yes yes newspap
Martin County Stuart FL FAU Boca Raton MIS Business NULL
NULL NULL NULL yes

Quote:

Originally Posted by Mateo

I having trouble formatting a report for a particular table. The table is quite large and has 20+ columns. Im trying to do a select * from the table, but the output is really bad. I know it has something to do with wrap around, but im not sure how to fix it. Ive increased the LINESIZE and PAGESIZE which helped, but its still impossible to read. The output looks like this:

APP_ID SSN AREA_CODE TEL_NO QUE QUE QUES_LEAVEREASON APPLY_POSITION START_DAT AMTHOURS_
---- ---- ---- ---- -- -- ------- ----- --- -
HIGH_NAME HIGH_CITY HIGH_STATE COLLEGE_NAME COLLEGE_LOCATION COLLEGE_MAJOR COL
------- ------- ----- ------- -------
EXTRA_ACTIVITIES OFFICES_HELD OTHER_SCHOOLING QUE
-------- -------- -------- --
80000 443264455 561 6548890 no no NULL Greeter 04-APR-04 40 yes yes walk-in
Chase HS Bloomfield IL NULL NULL NULL NULL NULL
NULL NULL NULL yes

80001 887651109 772 7817764 no no NULL Manager 12-DEC-05 40 yes yes friend
Broward County HS Palm Beach FL FAU Boca Raton Management Business NULL
NULL NULL NULL yes

80002 198667112 772 5443234 no no NULL Stocker 08-AUG-05 40 yes yes TV
Centiennal Port St. Lucie FL NULL NULL NULL NULL NULL
NULL NULL NULL yes

80003 332690823 561 7734456 no no NULL Security guard 01-JAN-04 40 yes yes
George OLeary Callamajo MD NULL NULL NULL NULL NULL
NULL NULL NULL yes

80004 557751123 772 2601973 no no NULL Cashier 07-JUL-07 30 yes yes friends
St.Lucie County Port St. Lucie FL FAU Boca Raton Dentistry Business NU
NULL NULL NULL yes

80005 554321123 561 7812238 no no NULL Manager 01-JUN-04 40 yes yes newspap
Martin County Stuart FL FAU Boca Raton MIS Business NULL
NULL NULL NULL yes

It depends on where and what you want to achieve. If you're in Query Analyzer, go to Query (menu) -> Results in grid. If you use it from some code, you'll have to work it out yourself - depending on what and where you need (sorry). Alternatively select fewer columns by listing the ones the you really need. Screen has limited width... ;-)|||I think you posted at the wrong section.
Sql plus is an oracle tool.
Just run the command from the prompt with the sqlplus.exe and redirect (>) it to an output file.

You should the be able to read the files more clearly (use the pspad viewer, it's free and it can handle long records)sql

formatting output

Hi, I am running a script which inserts certain rows into a table and at the end of the execution, I do a select statement to show the inserted data as output in the results pane and it is showing as truncated...How can I show the full results..
Here is my script to show the results.
----
SET NOCOUNT ON
DECLARE @.errorCount INT
SELECT @.errorCount = COUNT(*) FROM error_report WHERE id != - 2 AND id != - 5
IF @.errorCount = 0
BEGIN
INSERT INTO error_report VALUES( '' , - 1 , 'No error found.' )
END
INSERT INTO error_report VALUES( '' , - 2 , 'The Report was generated on ' + CAST(CONVERT(VARCHAR(23), GETDATE(), 1) AS VARCHAR) )
GO
SELECT table_name + ' table has bad data at id = ' + CAST(CONVERT(VARCHAR(23), id) AS VARCHAR) + ' (' + CAST(reason AS VARCHAR) + ')'FROM error_report WHERE id > 0
SELECT table_name + ' table has bad data (' + CAST(reason AS VARCHAR) + ')' FROM error_report WHERE id = 0
SELECT reason FROM error_report WHERE id = - 1
SELECT ''
SELECT reason FROM error_report WHERE id = - 2
SET NOCOUNT OFF
GO
--------
The Results in the bottom pane looks like this below
------
NODETABLE table has bad data (There are 2 duplicate subclass)
Propertytable table has bad data (at Parentid = 2000000859 and p)
Propertytable table has bad data (at Parentid = 10122 and proper)
------
But, when I do a select, they are like this below
--

NODETABLE 0 There are 2 duplicate subclass name: Specification
Propertytable 0 at Parentid = 2000000859 and propertyid = 721
Propertytable 0 at Parentid = 10122 and propertyid = 9That sounds like a grid limitation to me. I'd suggest Ctrl-T to set text mode, then Ctrl-E to execute the query again.

-PatP|||Hi Pat,
It outputted in the text format but the results are the same. However when I do select statement, it is fine.|||You need to explicitly specify the size for VARCHAR fields.|||Hi LOOKING AT THE ABOVE CODE, CAN YOU TELL ME WHERE IT IS?|||For example, here:

'The Report was generated on ' + CAST(CONVERT(VARCHAR(23), GETDATE(), 1) AS VARCHAR(8)) )

But you also don't need that CAST.

'The Report was generated on ' + CONVERT(VARCHAR(8), GETDATE(), 1)

Formatting Numbers in an SQL Statement

Hi,
I have a table that has an ID field which is automatically incremented as each new record is added, so if I do a SELECT * FROM Table1 I get:
ID, Name
1, Billy
2, Bob
3, Tony
You get the idea. What I want to do is format the number differently when it's returned from an SQL statement so I get:
ID, Name
0001, Billy
0002, Bob
0003, Tony
So I need something like SELECT FORMATNUMBER(ID, 4), Name FROM Table1 - Does anything like this exist?
Little 'un.This type of functionality is probably better served in your code, rather than on Sql. Sql is rather slow at doing string functions.
SELECT
RIGHT( '0000' + cast( ID as varchar), 4 ) ID
FROM
table1
bill|||

Hi Bill,

Thanks for that, unfortunately it's not something I can do in my ASP code, so that should work a treat.

Little'un

sql

formatting in sql server

hi

below is my query

select * from guest.tbl1

i m getting result like this

id score

1 50
2 100

3 150

4 200

but i want this output like this

1 50 2 100

3 150 4 200

how to do this?

thanx

Where you want to display this record?In a web page or in the SQL server o/p?|||in sql server o/p|||

Code Snippet

CREATE TABLE #TMP1(ID INT IDENTITY (1,1),COL1 INT,COL2 INT)
CREATE TABLE #TMP2(ID INT ,COL3 INT,COL4 INT)

INSERT INTO #TMP1
(
COL1
,COL2
)
SELECT id
,val
FROM tbl1

INSERT INTO #TMP2(ID,COL3
,COL4
)
SELECT ID
,COL1
,COL2 FROM #TMP1 WHERE ID%2=1

SELECT * FROM #TMP1
SELECT * FROM #TMP2
SELECT T2.COL3
,T2.COL4
,T1.COL1
,T1.COL2 FROM #TMP1 T1 JOIN #TMP2 T2 ON T1.ID = T2.ID + 1
GO
DROP TABLE #TMP1
DROP TABLE #TMP2

|||

Afraid to believe the ID value , It may have gaps rite?

Code Snippet

Create Table #data (

[id] int ,

[score] Varchar(100)

);

Insert Into #data Values('1','50');

Insert Into #data Values('2','100');

Insert Into #data Values('5','150');

Insert Into #data Values('6','200');

Insert Into #data Values('9','200');

Insert Into #data Values('10','200');

If you use SQL Server 2005,

Code Snippet

;WITH CTE

as

(

Select

*,

Row_Number() Over(Order By ID) RID

From

#data

)

Select up.id,up.score,dwn.id,dwn.score from CTE up

left outer join CTE dwn on up.Rid = dwn.Rid-1

Where

up. RID % 2 = 1

If you use SQL Server 2000,

Code Snippet

Declare @.Table Table(

[id] int ,

[score] Varchar(100) ,

[RID] int identity(1,1)

)

Insert Into @.Table

Select Id,Score From #Data Order By 1;

Select up.id,up.score,dwn.id,dwn.score from @.Table up

left outer join @.Table dwn on up.Rid = dwn.Rid-1

Where

up. RID % 2 = 1

formatting dates

Hi, I was wondering if there is a way to output a special format for dates in SELECT statements. Currently, my date field is returning my date in this format:
2004-01-19 00:00:00.000
but I want it to be like this:
1/19/04 or 1/19/2004
Is there a way to do this?
Thanks.Try this:

SELECT CONVERT(char(10),getdate(),103)|||Please.. use the 101 code for the date format..

like this:

SELECT CONVERT(char(10),getdate(),101)

the other example is another format.

You can consult all the date format in the SQL SERVER BOOKS ON LINE!.|||But doesn't that just return the current date?
Here's my SQL statement:

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, checkedOutDate, ReturnedDate FROM Checkouts";

checkedOutDate and ReturnedDate are the fields I want to format. How would this work with getdate()?|||Just replace the getdate() with checkedOutDate. Do the same again with ReturnedDate (a separate query).

Originally posted by domiflichi
But doesn't that just return the current date?
Here's my SQL statement:

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, checkedOutDate, ReturnedDate FROM Checkouts";

checkedOutDate and ReturnedDate are the fields I want to format. How would this work with getdate()?|||Look at my post from yesterday. I had the same question.
exdter|||That's what I thought you're supposed to do. But when I do that...here's my new SQL statement:

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, CONVERT(char(10),checkedOutDate,101), ReturnedDate FROM Checkouts

I get an error when trying to display my results of the query when encountering that field:

Warning: odbc_result(): Field checkedoutdate not found in d:\web\territories\index.php on line 173

If it helps: my webserver is Win2k Pro running Apache and PHP, and my SQL server is on Win2K server and it's actually MSDE 2000.|||Originally posted by exdter
Look at my post from yesterday. I had the same question.
exdter

I'm getting the same error message on my query:

Warning: odbc_result(): Field checkedoutdate not found in d:\web\territories\index.php on line 174

Here's the SQL statement:

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP, CONVERT(char(10),CONVERT(datetime, CAST(checkedOutDate as varchar(12))),103), ReturnedDate FROM Checkouts

Why isn't this working for me?
Thanks,
domiflichi|||Try this

SELECT id, FirstName, LastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, FirstNameLP, LastNameLP
, CONVERT(char(10),CONVERT(datetime, CAST(checkedOutDate as varchar(12))),103) AS checkedOutDate, ReturnedDate FROM Checkouts|||Thank you Brett!...it worked! And thank you to everybody else for getting me through each step, and being patient with this newbie. And thank you all for such quick replies!

Formatting Currency

Hi all,
I have a currency field in my database, when I select data from that
column, I want it to right align.
Any help?This is performed in the client application. SQL Server sends back data
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<riversmithco@.hotmail.com> wrote in message
news:1110819266.183608.268260@.z14g2000cwz.googlegroups.com...
> Hi all,
> I have a currency field in my database, when I select data from that
> column, I want it to right align.
> Any help?
>|||Which software do you use to display these values to the end users? Have you
considered researching the options on the client programming language or
report writer to see if they have provisions to support such alignment &
formatting functionality?
In SQL Server, such formatting requires you to do explicit type conversions
and padding up with spaces. For instance with a value like 123.45 you would
display it right aligned using a 10 character string value as:
DECLARE @.c MONEY
SET @.c = 123.45
SELECT RIGHT( SPACE(10) + CAST( @.c AS VARCHAR ), 10 )
Anith|||Tibor Karaszi wrote:
> This is performed in the client application. SQL Server sends back
data
But what if I am not using a client app, perhaps I am just running a
report from a sql query?|||Then use Anith's suggestion. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<riversmithco@.hotmail.com> wrote in message
news:1110820375.127912.77870@.g14g2000cwa.googlegroups.com...
> Tibor Karaszi wrote:
> data
>
> But what if I am not using a client app, perhaps I am just running a
> report from a sql query?
>

Formatting a Select Statement

Hi everyone!
I am looking for a function similar to the Left(), Right(), and
Replace() functions to insert characters into a returned list of my
date field. The date currently looks like this "06202006" the function
I am looking for would return "06/20/2006" something like this if
Insert() was an actual function:
SELECT DISTINCT insert("/", right(insert("/",left(date, 2)), 2)
FROM tblLogs;
Please let me know if you have any experience with this.Perhaps you are looking for STUFF:
SELECT STUFF(STUFF([date], 5, 0, '/'), 3, 0, '/')
FROM tblLogs;
However, data formatting is better done in the presentation layer rather
than the database code.
Hope this helps.
Dan Guzman
SQL Server MVP
"EESP" <johnson4@.wwu.edu> wrote in message
news:1150932662.524736.128910@.b68g2000cwa.googlegroups.com...
> Hi everyone!
> I am looking for a function similar to the Left(), Right(), and
> Replace() functions to insert characters into a returned list of my
> date field. The date currently looks like this "06202006" the function
> I am looking for would return "06/20/2006" something like this if
> Insert() was an actual function:
> SELECT DISTINCT insert("/", right(insert("/",left(date, 2)), 2)
> FROM tblLogs;
> Please let me know if you have any experience with this.
>|||Works Great! I would like to format in the presentation layer of the
code, but I'm having troble running that formatting code through a
datgrid returning close to 10K entries. For some reason just works a
lot faster server side. Thanks for the help!

Formatting a field to spec in SQL

Can anyone tell me how to format a field to spec?

Example:

Select num_field from table = 12345

I want

Select Format(num_field,"0000000000") from table = 0000012345

or some equivalent. I know in Access you could do this using the Format function, but I cannot seem to find anything like this in SQL.

Thanks in advance,

Michael

Hi,

there is no format function in SQL Server, you will have to do it this way here:

LEFT('0000000000' + CONVERT(VARCHAR(10),num_field), 10)

That will give you the appropiate format.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi Jen,

Thanks for the response, it works with one small exception, I need to use the RIGHT function not the LEFT. But all is good, and it works...THANKS!

Michael

|||You are sure right, my mistake :-)

formating in SqlServer vs Reporting Services

All else being equal, is it faster to use functions like ROUND and TRIM
in the SELECT statement of the query and let the db process it or to
set formating in the layout of the report and let Reporting Services
handle it?
In my case, all is not equal. We have the DB on a very powerful machine
that at sometimes is very overtaxed and runs smoothly at others. RS is
moving to a decently powerful server that it will have exclusively.
Thanks
Louis Ryder
SSRS Report DeveloperMy two cents. Formatting is a presentation concern. Rounding to 2 decimals
can be handled in the presentation layer (i.e., RS) using format strings.
TRIM isn't necessary unless you have some smokin' character columns...
-Tim
"Louis Ryder" <lroskind@.ryderauto.com> wrote in message
news:1152203807.627376.268390@.p79g2000cwp.googlegroups.com...
> All else being equal, is it faster to use functions like ROUND and TRIM
> in the SELECT statement of the query and let the db process it or to
> set formating in the layout of the report and let Reporting Services
> handle it?
> In my case, all is not equal. We have the DB on a very powerful machine
> that at sometimes is very overtaxed and runs smoothly at others. RS is
> moving to a decently powerful server that it will have exclusively.
> Thanks
> Louis Ryder
> SSRS Report Developer
>

Format using SQL server

I'm new to SQL server. My question here is how can I format the data that is retrieved as a result of SELECT.

Similar defining the column format in SQL Plus. For some reason SQL Server puts in extra spaces between the data fields in the .dat file.

If you have additional trainling spaces, it seems that you specified a fixed character length instead of a variable. THe data won′t be chopped until you will specify it either in the table structure or within the Select statement while pumpoing the data out of the database (CONVERT).

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||This is not with the data. Say if I have 2 fields Field 1 and Field 2. Field 1 is 12 char and field 2 is 2 char. Instead of putting Field 1 as 12 char it puts 15 char for Field 1 and for Field 2 it puts 4 char. I want to remove those extra spaces. I would appreciate any help.

Format output of FOR XML RAW

Hi! I use SELECT FOR XML RAW on MSSQL 2000.
I would like to be able to format the XML output so that instead of
having:
<row a="asd1"...>
<row a="asd2"...>
<row a="asd3"...>
I would get
<1 a="asd1"...>
<2 a="asd2"...>
<3 a="asd3"...>
or better yet be able to assign a specifc column value to replace
"row".
This is because I want to load the XML stream into LUA tables, and LUA
tables need unique indexes.
Thanks in advance.
Jeff Lambert
RAW mode in SQL2K generates elements with name 'row'. If you want to change
element name (custom name) you have to use EXPLICIT or AUTO (using aliases)
mode. In SQL 2005 you are able to give custom name with RAW clause.
/Patrick
"Jeff Lambert" <xradicalx@.gmail.com> wrote in message
news:f4f18e47.0410051032.ca9317d@.posting.google.co m...
> Hi! I use SELECT FOR XML RAW on MSSQL 2000.
> I would like to be able to format the XML output so that instead of
> having:
> <row a="asd1"...>
> <row a="asd2"...>
> <row a="asd3"...>
> I would get
> <1 a="asd1"...>
> <2 a="asd2"...>
> <3 a="asd3"...>
> or better yet be able to assign a specifc column value to replace
> "row".
> This is because I want to load the XML stream into LUA tables, and LUA
> tables need unique indexes.
> Thanks in advance.
> Jeff Lambert
|||Also note that <1 a="asd1"/> is not a well-formed XML element (names cannot
start with numbers).
If you want to generate something like <row1/><row2/> etc. you would need an
XSLT stylesheet that creates such elements and copies all the attributes
over.
What is LUA exactly?
Best regards
Michael
"Patrick Akerblom" <patrik.akerblom@.ontrax.se> wrote in message
news:%23m2N2x2qEHA.2900@.TK2MSFTNGP12.phx.gbl...
> RAW mode in SQL2K generates elements with name 'row'. If you want to
> change element name (custom name) you have to use EXPLICIT or AUTO (using
> aliases) mode. In SQL 2005 you are able to give custom name with RAW
> clause.
> /Patrick
> "Jeff Lambert" <xradicalx@.gmail.com> wrote in message
> news:f4f18e47.0410051032.ca9317d@.posting.google.co m...
>

format output as scientific notation (was "sql 2000 question")

SELECT membername, outputval
case when choice = 0 then outputval else null end as outputval
from MyDatabase
group by membername, outputval

how to format outputval:
if outputval < 40000
format outputval as:
5 - 5.78 - 6.9 - 6,778 - 4,567.8 - 12,456.78 - etc.
if outputval >= 40000
format it as a scientific.I would strongly suggest that you do the formatting at either the web server (using PHP), or better yet at the client (possibly using Javascript). This would allow you to use locale specific information that isn't usually available to SQL Server.

If you really have to format the output at the SQL Server, it could be done using a SQL UDF that called xp_sprintf or other related tools. This means that every query will need to be formatted using the locale of your SQL Server, which is usually a huge problem for application globalization.

-PatP

Format of DateTime types in Select vs Open Table

From within the SQL Server Management Studio, if I select a table and displa
y
it (right click Open Table), columns of type DateTime are displayed with the
mm/dd/yyyy hh:mm:ss format. However, if I SELECT * FROM the same table from
a
query in the Query Analyzer (or New Query in 2005), the same column is
displayed with a yyyy-mm-dd hh:mm:ss format.
Why is there a difference in display format between opening the table in the
Management Studio and SELECT'ing in a query?
Short of using a CONVERT in the SELECT, is there a way of controlling the
display format of DateTime types in both scenarios?
Michael
--
Michael Hocksteinmichael (howlinghound@.nospam.nospam) writes:
> From within the SQL Server Management Studio, if I select a table and
> display it (right click Open Table), columns of type DateTime are
> displayed with the mm/dd/yyyy hh:mm:ss format. However, if I SELECT *
> FROM the same table from a query in the Query Analyzer (or New Query in
> 2005), the same column is displayed with a yyyy-mm-dd hh:mm:ss format.
> Why is there a difference in display format between opening the table in
> the Management Studio and SELECT'ing in a query?
Open Table respects the regional settings, while by default Query Analyzer
does not. You can change this for QA by going into Tools->Options->
Connections and check "Use regional settings...". There does not seem to be
any similar option for Management Studio. but query results always
apparently uses ISO format. (I can't really tell from here, because my
regional settings are the ISO format.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you!
Michael Hockstein
"Erland Sommarskog" wrote:

> michael (howlinghound@.nospam.nospam) writes:
> Open Table respects the regional settings, while by default Query Analyzer
> does not. You can change this for QA by going into Tools->Options->
> Connections and check "Use regional settings...". There does not seem to b
e
> any similar option for Management Studio. but query results always
> apparently uses ISO format. (I can't really tell from here, because my
> regional settings are the ISO format.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Format of DateTime Column in SELECT query

I am using SQL2005 and ASP.NET 2.0

I have one column in database called DateTime and it is defined like type datetime.It is formated like: day.month.year hour:minutes:seconds

My question is: I want to get date from Column DateTime in format day.month.year in SELECT query, not in stored procedure.

thanks

SELECT

CONVERT(NVARCHAR(10),getdate(), 104)as yourDate

replace the getdate() function with your datetime field in your select statement.

|||I just want to remind you that Column Name DateTime is the keyword of sql. You have to put it in [] or ""|||thanx, i managed it somehow.I found instructions in book about CONVERT so i saw how it works

format for Convert

Hello,

SELECT name + '- ' + CONVERT(varchar,amt) as ddlCap from myTable

How can I get amt be like "#0.00" format in ddlCap?

Hi,

Please run the below statement to see if it is what you want...

SELECT
amt,
CONVERT(varchar(20),amt,0),
CONVERT(varchar(20),amt,1)
FROM myTable

Eralper
http://www.kodyaz.com

format

Hi,
select SUM(CommAmt * (1 - Disc/100.00)) AS Comm from mytbl return:
24.6199951171875
how to format it so it will only return two decimal? Thanks.CONVERT(DECIMAL(5,2), expression)
"js" <js@.someone@.hotmail.com> wrote in message
news:O$0z8W9tFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi,
> select SUM(CommAmt * (1 - Disc/100.00)) AS Comm from mytbl return:
> 24.6199951171875
> how to format it so it will only return two decimal? Thanks.
>
>