MBR IT/.NET 247 : Formatting data in DataColumn on ASPFriends.com 'ngfx-sqlclient' list

Formatting data in DataColumn

Messages

Related Types

This message was discovered on ASPFriends.com 'ngfx-sqlclient' list.

Donovan Lowe

-- Moved from [aspngdata] to [ngfx-sqlclient] by Tony Stark <Click here to reveal e-mail address> --

Hi there

I hope I'm posting this in the right place! What I'm trying to do is this:

I'm importing SQL into various tables in a DataSet from a SQL2K Database,
creating nested relations, and then ouputting XML via a XmlDataDocument. The
problem is that all my date values (which are correctly formatted in the SQL
DB) are ending up in the wrong format in the XML output (with time values
etc which I don't need).

Is there some way of specifying the format of DateTime data in a given
DataColumn, or is there another way to solve the problem? If anyone could
help, that would be great!

Cheers
Don Lowe

Reply to this message...

Peter Brunone

Don,

    This is a common problem; I've come across it several times in the last few
weeks alone. Since you're going straight from SQL Server to the output
file, the options are limited, but you still have a few viable choices.
    Most likely, you're going to have to select the DATEPART pieces (or DAY,
MONTH, and YEAR) and manipulate them as needed in your query. This isn't
very difficult with the help of your SQL Server Books Online; just search
for the pertinent terms (you may want to use DATEPART since it gives you
greater formatting flexibility), concatenate, and alias the resulting value
with the appropriate column name. For example, if you had a field "MyDate"
with a value of April 9, 1998, then

SELECT DATEPART(mm, MyDate) + '/' + DATEPART(dd, MyDate) + '/' +
DATEPARTDATEPART(yyyy, MyDate) AS FormattedDate

would produce a field called "FormattedDate" with a value of 04/09/1998.
    Of course, I don't know if the XmlDataDocument is going to mess up your
formatting either, but that's a question for another list.

Regards,

Peter Brunone

|-----Original Message-----
|From: Donovan Lowe [mailto:Click here to reveal e-mail address]
|
|Hi there
|
|I hope I'm posting this in the right place! What I'm trying to do is this:
|
|I'm importing SQL into various tables in a DataSet from a SQL2K Database,
|creating nested relations, and then ouputting XML via a
|XmlDataDocument. The
|problem is that all my date values (which are correctly formatted
|in the SQL
|DB) are ending up in the wrong format in the XML output (with time values
|etc which I don't need).
|
|Is there some way of specifying the format of DateTime data in a given
|DataColumn, or is there another way to solve the problem? If anyone could
|help, that would be great!
|
|Cheers
|Don Lowe
|

Reply to this message...

Marisk

or

CONVERT (char (16), date, 101)

where datecolumn is the name of the column

----- Original Message -----
From: "Peter Brunone" <Click here to reveal e-mail address>
To: "ngfx-sqlclient" <Click here to reveal e-mail address>
Sent: Tuesday, June 25, 2002 1:05 PM
Subject: [ngfx-sqlclient] RE: Formatting data in DataColumn

[Original message clipped]

Reply to this message...

Russ McClelland

TXVjaCBlYXNpZXI6DQooaW4gY29kZSkNCnRoaXMudGJsUHJldmlvdXNTYWxhcnlBY3Rpb24uUm93
c1sxXS5DZWxsc1sxXS5UZXh0ID0NCmFuRW1wbG95ZWUuUHJldmlvdXNTYWxhcnlBY3Rpb24uRGF0
ZS5Ub1N0cmluZyggImQiICk7DQoNCm9yIChpbiBIVE1MIHNldHRpbmdzKQ0KIA0KPGFzcDpCb3Vu
ZENvbHVtbiBEYXRhRm9ybWF0U3RyaW5nPSJ7MDovTU0vZGQveXl9Ig0KIA0KIA0KDQoJLS0tLS1P
cmlnaW5hbCBNZXNzYWdlLS0tLS0gDQoJRnJvbTogUGV0ZXIgQnJ1bm9uZSANCglTZW50OiBUdWUg
Ni8yNS8yMDAyIDM6MDUgUE0gDQoJVG86IG5nZngtc3FsY2xpZW50IA0KCUNjOiANCglTdWJqZWN0
OiBbbmdmeC1zcWxjbGllbnRdIFJFOiBGb3JtYXR0aW5nIGRhdGEgaW4gRGF0YUNvbHVtbg0KCQ0K
CQ0KDQoJRG9uLA0KCQ0KCSAgICAgICAgVGhpcyBpcyBhIGNvbW1vbiBwcm9ibGVtOyBJJ3ZlIGNv
bWUgYWNyb3NzIGl0IHNldmVyYWwNCnRpbWVzIGluIHRoZSBsYXN0IGZldw0KCXdlZWtzIGFsb25l
LiAgU2luY2UgeW91J3JlIGdvaW5nIHN0cmFpZ2h0IGZyb20gU1FMIFNlcnZlciB0byB0aGUNCm91
dHB1dA0KCWZpbGUsIHRoZSBvcHRpb25zIGFyZSBsaW1pdGVkLCBidXQgeW91IHN0aWxsIGhhdmUg
YSBmZXcgdmlhYmxlDQpjaG9pY2VzLg0KCSAgICAgICAgTW9zdCBsaWtlbHksIHlvdSdyZSBnb2lu
ZyB0byBoYXZlIHRvIHNlbGVjdCB0aGUgREFURVBBUlQNCnBpZWNlcyAob3IgREFZLA0KCU1PTlRI
LCBhbmQgWUVBUikgYW5kIG1hbmlwdWxhdGUgdGhlbSBhcyBuZWVkZWQgaW4geW91ciBxdWVyeS4N
ClRoaXMgaXNuJ3QNCgl2ZXJ5IGRpZmZpY3VsdCB3aXRoIHRoZSBoZWxwIG9mIHlvdXIgU1FMIFNl
cnZlciBCb29rcyBPbmxpbmU7DQpqdXN0IHNlYXJjaA0KCWZvciB0aGUgcGVydGluZW50IHRlcm1z
ICh5b3UgbWF5IHdhbnQgdG8gdXNlIERBVEVQQVJUIHNpbmNlIGl0DQpnaXZlcyB5b3UNCglncmVh
dGVyIGZvcm1hdHRpbmcgZmxleGliaWxpdHkpLCBjb25jYXRlbmF0ZSwgYW5kIGFsaWFzIHRoZQ0K
cmVzdWx0aW5nIHZhbHVlDQoJd2l0aCB0aGUgYXBwcm9wcmlhdGUgY29sdW1uIG5hbWUuICBGb3Ig
ZXhhbXBsZSwgaWYgeW91IGhhZCBhDQpmaWVsZCAiTXlEYXRlIg0KCXdpdGggYSB2YWx1ZSBvZiBB
cHJpbCA5LCAxOTk4LCB0aGVuDQoJDQoJU0VMRUNUIERBVEVQQVJUKG1tLCBNeURhdGUpICsgJy8n
ICsgREFURVBBUlQoZGQsIE15RGF0ZSkgKyAnLycgKw0KCURBVEVQQVJUREFURVBBUlQoeXl5eSwg
TXlEYXRlKSBBUyBGb3JtYXR0ZWREYXRlDQoJDQoJd291bGQgcHJvZHVjZSBhIGZpZWxkIGNhbGxl
ZCAiRm9ybWF0dGVkRGF0ZSIgd2l0aCBhIHZhbHVlIG9mDQowNC8wOS8xOTk4Lg0KCSAgICAgICAg
T2YgY291cnNlLCBJIGRvbid0IGtub3cgaWYgdGhlIFhtbERhdGFEb2N1bWVudCBpcyBnb2luZw0K
dG8gbWVzcyB1cCB5b3VyDQoJZm9ybWF0dGluZyBlaXRoZXIsIGJ1dCB0aGF0J3MgYSBxdWVzdGlv
biBmb3IgYW5vdGhlciBsaXN0Lg0KCQ0KCVJlZ2FyZHMsDQoJDQoJUGV0ZXIgQnJ1bm9uZQ0KCQ0K
CXwtLS0tLU9yaWdpbmFsIE1lc3NhZ2UtLS0tLQ0KCXxGcm9tOiBEb25vdmFuIExvd2UgWyBtYWls
dG86ZG9ub3ZhbkBzdWJtYXJpbmVsdGQuY29tXQ0KCXwNCgl8SGkgdGhlcmUNCgl8DQoJfEkgaG9w
ZSBJJ20gcG9zdGluZyB0aGlzIGluIHRoZSByaWdodCBwbGFjZSEgV2hhdCBJJ20gdHJ5aW5nIHRv
DQpkbyBpcyB0aGlzOg0KCXwNCgl8SSdtIGltcG9ydGluZyBTUUwgaW50byB2YXJpb3VzIHRhYmxl
cyBpbiBhIERhdGFTZXQgZnJvbSBhIFNRTDJLDQpEYXRhYmFzZSwNCgl8Y3JlYXRpbmcgbmVzdGVk
IHJlbGF0aW9ucywgYW5kIHRoZW4gb3VwdXR0aW5nIFhNTCB2aWEgYQ0KCXxYbWxEYXRhRG9jdW1l
bnQuIFRoZQ0KCXxwcm9ibGVtIGlzIHRoYXQgYWxsIG15IGRhdGUgdmFsdWVzICh3aGljaCBhcmUg
Y29ycmVjdGx5DQpmb3JtYXR0ZWQNCgl8aW4gdGhlIFNRTA0KCXxEQikgYXJlIGVuZGluZyB1cCBp
biB0aGUgd3JvbmcgZm9ybWF0IGluIHRoZSBYTUwgb3V0cHV0ICh3aXRoDQp0aW1lIHZhbHVlcw0K
CXxldGMgd2hpY2ggSSBkb24ndCBuZWVkKS4NCgl8DQoJfElzIHRoZXJlIHNvbWUgd2F5IG9mIHNw
ZWNpZnlpbmcgdGhlIGZvcm1hdCBvZiBEYXRlVGltZSBkYXRhIGluDQphIGdpdmVuDQoJfERhdGFD
b2x1bW4sIG9yIGlzIHRoZXJlIGFub3RoZXIgd2F5IHRvIHNvbHZlIHRoZSBwcm9ibGVtPyBJZg0K
YW55b25lIGNvdWxkDQoJfGhlbHAsIHRoYXQgd291bGQgYmUgZ3JlYXQhDQoJfA0KCXxDaGVlcnMN
Cgl8RG9uIExvd2UNCgl8DQoJDQoJDQoJfCBbbmdmeC1zcWxjbGllbnRdIG1lbWJlciBydXNzLm1j
Y2xlbGxhbmRAc21hcnRvYmp4LmNvbSA9IFlPVVINCklEDQoJfCBodHRwOi8vd3d3LmFzcGZyaWVu
ZHMuY29tL2FzcGZyaWVuZHMvbmdmeC1zcWxjbGllbnQuYXNwID0NCkpPSU4vUVVJVA0KCQ0KDQo

Reply to this message...

	System.Data.DataColumn
	System.Data.DataSet
	System.DateTime
	System.Xml.XmlDataDocument

MBR BootFX
Best-of-breed application framework for .NET projects, developed by Matthew Baxter-Reynolds and MBR IT

	Copyright © Matthew Baxter-Reynolds 2001-2008. '.NET 247 Software Development Services' is a trading style of MBR IT Solutions Ltd.
	Contact Us - Terms of Use - Privacy Policy - www.dotnet247.com