date format problem

i have two fields, one is startDate and other is endDate, i need only date part in start date and date and time in end date, t shows correctly in db but when i write it in xml (using asp.net) the results are as

<startDate>2003-11-16T00:00:00.0000000+05:00</startDate>
<endDate>2003-11-24T00:00:00.0000000+05:00</endDate>That is the default display format for XML. You have a couple of options when dealing with dates in XML:

1. Write the data to XML as a character string (since you only need the date part, you could use SELECT Convert(Varchar(10), MyDate, 101).

2. When you display the XML (or parse it) you can use an XSL transform to format it (I think the XSL function is formatdate). There is a separate XSL function to handle time, IIRC.

Regards,

hmscott

Originally posted by waqas
i have two fields, one is startDate and other is endDate, i need only date part in start date and date and time in end date, t shows correctly in db but when i write it in xml (using asp.net) the results are as

<startDate>2003-11-16T00:00:00.0000000+05:00</startDate>
<endDate>2003-11-24T00:00:00.0000000+05:00</endDate>|||how to write it in sql query form
update t_auction set [status]= 'closed' where endDate < GETDATE()|||I'm sorry, I don't think I understand your question. Are you using the OPENXML method to get data from an XML document? Or is the XML date (2003-11-24T00:00:00.0000000+05:00) a value in a column in a SQL table that you are trying filter by?

Sorry, scratching my head on this one.

hmscott

Originally posted by waqas
how to write it in sql query form
update t_auction set [status]= 'closed' where endDate < GETDATE()|||i have to store the result in an xml file but before that i have to change the stauts from open to close whose date has been expired. in either cases i.e changing status and writing to xml file has this date probleem.|||can you post some more information? tables, scripts, etc.

thnx.

Originally posted by waqas
i have to store the result in an xml file but before that i have to change the stauts from open to close whose date has been expired. in either cases i.e changing status and writing to xml file has this date probleem.