xml query datetime datatype problem while inserting to xml

Question

xml query datetime datatype problem while inserting to xml

IT researcher

SSCertifiable

Points: 7567
More actions
March 2, 2013 at 2:57 am

#274807

I have a query like
UPDATE a
SET oldRemarks1.modify('insert <Value_Py>{sql:column("b.collection_date")}</Value_Py>
after (/Root/Row[3]/Item)[1]') from b
where a.[Date]='31-May-2004' and a.sl_no=b.regkey_slno
Here collection_date is of datetime datatype.So while it is inserted to xml it will be like 2012-03-05T00:00:00.000 in the xml tag.But i want to insert it as just "2012-03-05" in the xml tag. So how can i use cast or any other method here so that it will insert properly in xml.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

winmansoft (3/2/2013)
I have a query like
UPDATE a
SET oldRemarks1.modify('insert <Value_Py>{sql:column("b.collection_date")}</Value_Py>
after (/Root/Row[3]/Item)[1]') from b
where a.[Date]='31-May-2004' and a.sl_no=b.regkey_slno
Here collection_date is of datetime datatype.So while it is inserted to xml it will be like 2012-03-05T00:00:00.000 in the xml tag.But i want to insert it as just "2012-03-05" in the xml tag. So how can i use cast or any other method here so that it will insert properly in xml.

Easiest answer, don't. Store the date in the proper data format, which is what is happening. If you must store it in a character format (not a good idea), then store it using a character data type.

IT researcher SSCertifiable Points: 7567 More actions · Answer 2

I can't change the datatype from datetime to date.So is there any method like cast or anything?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

winmansoft (3/5/2013)
I can't change the datatype from datetime to date.So is there any method like cast or anything?

Then think of this like the datetime data type in SQL. You may want to store only '2012-03-05' in the column but when ever you query the column you see '2012-03-05 00:00:00.000'. The XML data type is the same way. It is still going to have a time portion to it.