How can I format dates with FOR XML? (10912 Requests)

If you have a datetime column in your select you will end up with a strange looking format for your dates. Something like 2001-10-17T09:30:12.

To avoid this you need to use the CONVERT fuction. So instead of

select ...
mydate as [order!1!date]

you would use

select ...
convert(varchar(x), mydate, format) as [order!1!date],

where x is the size of the string you want to return and format is the date format you want (see the table of formats found at the url below).

Feedback

# How can I format dates with FOR XML? 7/30/2001 8:14 AM Roman S. Golubin

Hi, sqlxml! I would like to add to told by you above the following: If value of a variable of type date Is Null then value '1900-01-01T00:00:00' will be returned. What to avoid it's I recommend to correct a code on below-mentioned: select... Coalesce(CONVERT(varchar(x), mydate, format), '') -- WBR Roman S. Golubin golubinrs@arhcity.ru

# How can I format dates with FOR XML? 7/31/2001 10:20 AM Bryant

Thanks for the feedback! However, the result of convert(varchar(20), null, 101) is actually just null. So the only time you would get the '1900-01-01T00:00:00' is if your enter a blank into a datetime column in which case coalesce would still return that same value.

# That strange looking format is ISO 8601 2/5/2002 8:10 PM George

Instead of bypassing it with workarounds you should implement it in everything you do. Further reading: http://www.google.com/search?q=iso+8601

# Don't do it this way: 4/6/2004 10:31 AM Stu

I read on here somewhere to do a

set column=convert(datetime,replace(xml.column,'T',' '))

within the update statement when you do the update inner join style.

Well, I'm now having to go back over several thousand rows of data to fix the results of this. This method seems to strip out second data. BAD!

I have switched to this method and all seems to be working:

set column=convert(datetime,xml.column,126)

126 is the style modifier for ISO8601

# re: How can I format dates with FOR XML? 5/4/2004 5:06 AM max

somebody can post an example of this tag ?

im using

http://mywebsite/?sql=select%20*%20,CONVERT(datetime,DATA,103)as[order!1!date]%20FROM%20NOTIFURBANA%20FOR%20XML%20AUTO&root=root

and is not working

# re: How can I format dates with FOR XML? 5/6/2004 7:47 AM BriK

How about this...TSQL Case/When:
ReviewPeriodBeginDate =
CASE
WHEN ReviewPeriodBeginDate IS NOT NULL THEN CONVERT(varchar
(10),ReviewPeriodBeginDate,101)

ELSE '' END,