I have a SPROC which Im using to get data into a datatable (.NET) for creating an XML sitemap file, I found some SQL code for formatting the date but isn't working correctly:
(SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS '[YYYY-MM-DD]') AS lastmod,
Ive also used this against other smalldatetime values in Union all queries,
it should rather obviously be 2008-11-03 and end up in the XML as
but its ending up in the file as <LastMod>11/02/08</LastMod>
this information is coming directly from SQLServer, as I don't touch the dates in the application layer, and use the datatable.writexml method.