FOR XML vs. ADO

Hi,
Not sure if this is the best newsgroup to ask this so let me know if there
is a better one. I am in the process of investigating converting a componen
t
which uses ADO recordsets to return data from a DB, to making use of the FOR
XML statement. When using recordsets the default date format which is outpu
t
is DD/MM/YYYY hh:mm:ss. However, when using FOR XML the output format is
YYYY-MM-DDThh:mm:ss. This is a bit of pain to be honest since the formats
need to be the same to ensure backwards compatibility. What are the reasons
for the differences? What would be the best way to perform the formatting o
f
the FOR XML output, taking into account that performance is paramount and
that any SQL conversions would have to be propogated to all of the existing
SQL stored procs/views?
Thanks.> When using recordsets the default date format which is output
> is DD/MM/YYYY hh:mm:ss.
That is not correct. When the data leaves SQL Server, it has no format. It i
s the client application
that formats the data. Your assumption of the above format is probably becau
se you had a regional
setting on the machine where you ran the client app that formatted the data
in such way.
For XML, it is a different story, as XML is not binary data (which datetime
is). So a format had to
be chosen, and the most reasonable choice would be the international standar
d for datetime
formatting, ISO 8601, which I believe also is common in other XML implementa
tions. I do not
recommend changing the formatting in your XML document as that would divert
from accepted standards.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:139B3299-FF59-4F4D-9D6E-CD502F4DDD70@.microsoft.com...
> Hi,
> Not sure if this is the best newsgroup to ask this so let me know if there
> is a better one. I am in the process of investigating converting a compon
ent
> which uses ADO recordsets to return data from a DB, to making use of the F
OR
> XML statement. When using recordsets the default date format which is out
put
> is DD/MM/YYYY hh:mm:ss. However, when using FOR XML the output format is
> YYYY-MM-DDThh:mm:ss. This is a bit of pain to be honest since the formats
> need to be the same to ensure backwards compatibility. What are the reaso
ns
> for the differences? What would be the best way to perform the formatting
of
> the FOR XML output, taking into account that performance is paramount and
> that any SQL conversions would have to be propogated to all of the existin
g
> SQL stored procs/views?
> Thanks.|||Regardless of what format the data has when it leaves SQL Server ADO formats
the date into the DD/MM/YYYY hh:mm:ss format by default, on our machines. B
y
using FOR XML I can speed up the component by approx 10% but the format must
remain the same. If this means breaking an accepted standard then so be it,
if a different format was returned it would essentially mean that every
single object within the business logic tier would have to checked and
altered to ensure that the new format is supported.
So taking the above into account do you have any suggestions for the second
question - What would be the best way to perform the formatting of the FOR
XML output, taking into account that performance is paramount and that any
SQL conversions would have to be propogated to all of the existing SQL store
d
procs/views?
"Tibor Karaszi" wrote:

> That is not correct. When the data leaves SQL Server, it has no format. It
is the client application
> that formats the data. Your assumption of the above format is probably bec
ause you had a regional
> setting on the machine where you ran the client app that formatted the dat
a in such way.
> For XML, it is a different story, as XML is not binary data (which datetim
e is). So a format had to
> be chosen, and the most reasonable choice would be the international stand
ard for datetime
> formatting, ISO 8601, which I believe also is common in other XML implemen
tations. I do not
> recommend changing the formatting in your XML document as that would diver
t from accepted standards.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Lee" <Lee@.discussions.microsoft.com> wrote in message
> news:139B3299-FF59-4F4D-9D6E-CD502F4DDD70@.microsoft.com...
>|||Why would you think that datetime values need to be formatted at all - other
than for presentation purposes?
You could convert datetime values to character data and format it but that
would just break the domain. I.e. you'd have to convert it back in order to
use it programatically.
What exactly are you trying to achieve?
ML
http://milambda.blogspot.com/|||Lee (Lee@.discussions.microsoft.com) writes:
> Regardless of what format the data has when it leaves SQL Server ADO
> formats the date into the DD/MM/YYYY hh:mm:ss format by default, on our
> machines. By using FOR XML I can speed up the component by approx 10%
> but the format must remain the same. If this means breaking an accepted
> standard then so be it, if a different format was returned it would
> essentially mean that every single object within the business logic tier
> would have to checked and altered to ensure that the new format is
> supported.
> So taking the above into account do you have any suggestions for the
> second question - What would be the best way to perform the formatting
> of the FOR XML output, taking into account that performance is paramount
> and that any SQL conversions would have to be propogated to all of the
> existing SQL stored procs/views?
You can use the convert function in a query force a certain date format,
look up "CAST and CONVERT" in Books Online. But it is an extremely poor
idea to do, because that means you are shoving a date format down the
throat of your users. The likelihood that I will use your is likely to
be miniscule, but I want to see dates formatted as YYYY-MM-DD.
So for a good implementation, you should recevie the XML document as you
do now, and then convert the date client-side according to the regional
settings.
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|||By no means is this an ideal scenario, I understand that. The framework I a
m
working with is a number of years old and I fear that the business objects
expect a certain date format which means that if this new date format was
used everything would go boom.
The only thing I was trying to acheive was a small performance increase
without having to change the code in too many components which means that I
would have to maintain exactly the same input and output. FOR XML provides
a
nice performance boost but the output is not exactly the same hence risking
the breaking of other components.
"ML" wrote:

> Why would you think that datetime values need to be formatted at all - oth
er
> than for presentation purposes?
> You could convert datetime values to character data and format it but that
> would just break the domain. I.e. you'd have to convert it back in order t
o
> use it programatically.
> What exactly are you trying to achieve?
>
> ML
> --
> http://milambda.blogspot.com/|||Maybe I didnt explain the situation correctly. This has nothing to do with
the presentation or what the user sees. This is to do with the format of
date that the business objects depend on to perform the required processing.
If these existing components suddenly get a new date format then it would
bring everything tumbline down hence why it is imperative that the output
from the data access component remains the same as to ensure full
compatibility.
I get the impression from the various replies that what I want is not
possible without making alterations to several other pieces of code.
"Erland Sommarskog" wrote:

> Lee (Lee@.discussions.microsoft.com) writes:
> You can use the convert function in a query force a certain date format,
> look up "CAST and CONVERT" in Books Online. But it is an extremely poor
> idea to do, because that means you are shoving a date format down the
> throat of your users. The likelihood that I will use your is likely to
> be miniscule, but I want to see dates formatted as YYYY-MM-DD.
> So for a good implementation, you should recevie the XML document as you
> do now, and then convert the date client-side according to the regional
> settings.
>
> --
> 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
>|||> I get the impression from the various replies that what I want is not
> possible without making alterations to several other pieces of code.
To the best of my knowledge, there's no setting for this. The only option I
can think of would be to
indeed have CONVERT around the datetime column in every SELECT statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:6AD5B648-3CCF-4368-A4C8-741D28F253E8@.microsoft.com...
> Maybe I didnt explain the situation correctly. This has nothing to do wit
h
> the presentation or what the user sees. This is to do with the format of
> date that the business objects depend on to perform the required processin
g.
> If these existing components suddenly get a new date format then it would
> bring everything tumbline down hence why it is imperative that the output
> from the data access component remains the same as to ensure full
> compatibility.
> I get the impression from the various replies that what I want is not
> possible without making alterations to several other pieces of code.
> "Erland Sommarskog" wrote:
>|||Lee (Lee@.discussions.microsoft.com) writes:
> Maybe I didnt explain the situation correctly. This has nothing to do
> with the presentation or what the user sees. This is to do with the
> format of date that the business objects depend on to perform the
> required processing.
A horrible application design, in my opinion. If someone would change
the regional settings to US English, your business objects would choke -
or even worse: misinterpret the dates.

> If these existing components suddenly get a new date format then it would
> bring everything tumbline down hence why it is imperative that the output
> from the data access component remains the same as to ensure full
> compatibility.
> I get the impression from the various replies that what I want is not
> possible without making alterations to several other pieces of code.
Since you were to introduce XML into the pot, it appears that you are
into changing code anyway. You can get back dates in various formats
with XML, if you use the convert() function to format them as strings
in SQL Server.
But if you add XML, you will have to change the code client-side as
well, so I don't really see the problem with converting dates from
the XML documents to the regional settings.
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