Modifying an XML - SMALLDATETIME

Question

Hi I am working with XML in SQL server and trying to use the modify function on a SMALLDATETIME variable but it does not give me desired result.

    DECLARE @Shift_StartDate SMALLDATETIME = '2017-06-05 09:00:00';
DECLARE @Shift_EndDate SMALLDATETIME = '2017-06-05 09:30:00';
DECLARE @x XML = '<dbo.tblXXXX StartDate="" EndDate=""  />'
SET @x.modify('replace value of (/*[1]/@StartDate) with sql:variable("@Shift_StartDate")')
SET @x.modify('replace value of (/*[1]/@EndDate) with sql:variable("@Shift_EndDate")')

SELECT @X

The output is. There are additional '.000' appended at the end. How can I get rid of them.

<dbo.tblXXXX StartDate="2017-06-05T09:00:00.000" EndDate="2017-06-05T09:30:00.000" />

I want the above output to like as shown below as this is what is returned when i run the below query

<dbo.tblXXXX StartDate="2017-06-05T09:00:00" EndDate="2017-06-05T09:30:00" />

DECLARE @ActualXml XML;

SET @ActualXml = (SELECT 
                     [StartDate], 
                     [EndDate]
                     FROM [dbo].[tblXXXX]
                     WHERE UniqueId = 8124X
                     FOR XML AUTO)
SELECT @ActualXml

DataType of [StartDate] and [EndDate] is 'SMALLDATETIME'

I want the XML modify function to return the same result without .000.

Please suggest. Thanks in advance Rao

By default it is returning<_x0023_temp StartDate="2017-06-05T09:00:00" /> — Kannan Kandasamy, Jun 28 '17 at 19:37
My issue is with the XML modify function which adds .000 at the end. — BRDroid, Jun 28 '17 at 19:38
@KannanKandasamy when you run the first query you get a result as XML. If you click the XML link you will see that the format has the extra .000 — Jacob H, Jun 28 '17 at 19:40
It appears SQL is doing an implicit conversion from smalldatetime to xs:datetime. The only way I seem to be able to work around this is to declare the variables as varchar. Adding a `cast` to the xquery modify command doesn't seem to cut it. — Jacob H, Jun 28 '17 at 19:46
Hi Jacob. Thanks you for your response. Can you provide an example of adding cast to xquery modify command. I did not get that bit. — BRDroid, Jun 28 '17 at 19:50
`SET @x.modify('replace value of (/*[1]/@StartDate) with sql:variable("@Shift_StartDate") cast as xs:string ?')` I tried this, but still it has the trailing .000. — Jacob H, Jun 28 '17 at 19:52
Documentation here: https://learn.microsoft.com/en-us/sql/xquery/type-casting-rules-in-xquery — Jacob H, Jun 28 '17 at 19:53
`DECLARE @Shift_StartDate varchar(24) = '2017-06-05 09:00:00';` changing the declaration to varchar(24) instead of smalldatetime *does* work though. Results: `` — Jacob H, Jun 28 '17 at 19:54
That might be a problem because the select query returns . There is a 'T' added between date and the time. which is done by FOR XML AUTO. So in this case too both wont match — BRDroid, Jun 28 '17 at 19:58
Using varchar variable does not give me the "T". Maybe a collation issue? — Jacob H, Jun 28 '17 at 19:59
@JacobH **Within** XML a date should be `ISO8601`, which packs a `T` between the date and the time. Although it might work, I would not recommend it. You might convert the date to a ISO8601-date externally with `CONVERT` and formatcode `126`, cut it to the proper size and insert *this* as `(N)VARCHAR` variable. But this seems a bit clumsy... — Shnugo, Jun 29 '17 at 07:40
@Rao, The `T` within the `datetime` value is perfectly okay and part of `ISO8601` specification. — Shnugo, Jun 29 '17 at 07:41
@Shnugo Does using CONVERT give the results the user is looking for? — Jacob H, Jun 29 '17 at 12:33
@JacobH, well, as told above: As long as the variable you use within `sql:variable()` is a **typed date(time)** the engine will use a defined conversion to get a formalized textual representation of the given value. This is necessary in order to read the values properly. You can store literally any date-format, even completely invalid or wrong values, if you introduce them as strings. So: Yes, you can create the needed format externally and insert it as string. But I would not reccomend this. See my answer for a better approach. — Shnugo, Jun 29 '17 at 13:08
So the answer is no, you can't use CONVERT to get the format in the XML file as you suggested. You have to truncate the date, which is what I suggested, you just did it as part of the xquery and I changed the input variable. I know it feels good to be "right" and all... — Jacob H, Jun 29 '17 at 13:11
@JacobH I don't know, if I get this correctly, especially your last sentence... It is a big difference between *I insert a string which looks like a date* and *I cut the textual representation of a date **within** XML*. It seems, that `modify()` does not distinguish between `datetime` and `smalldatetime`. Both would work, but the first *can* lead to unexpected and strange formats and needs a multi-step approach. The second is still type-safe and can be done fully inlined... Properly done it's a matter of taste... — Shnugo, Jun 29 '17 at 17:42

score 0 · Answer 1 · answered Jun 29 '17 at 07:37

You might cast the value to xs:string and use XQuery's substring to cut off the string:

DECLARE @Shift_StartDate SMALLDATETIME = '2017-06-05 09:00:00';
DECLARE @Shift_EndDate SMALLDATETIME = '2017-06-05 09:30:00';
DECLARE @x XML = '<dbo.tblXXXX StartDate="" EndDate=""  />'
SET @x.modify('replace value of (/*[1]/@StartDate) with substring(xs:string(sql:variable("@Shift_StartDate")),1,19)')
SET @x.modify('replace value of (/*[1]/@EndDate) with sql:variable("@Shift_EndDate")')

SELECT @X

The result

<dbo.tblXXXX StartDate="2017-05-06T09:00:00" EndDate="2017-05-06T09:30:00.000" />

StartDate is without .000, while EndDate is unchanged

Modifying an XML - SMALLDATETIME

1 Answers1