Using SQL datetime and smalldatetime values inside XQuery

I had a request today asking me how to get the current date/time as part of an XQuery expression in SQL Server 2005. The XQuery standard defines various functions to do this type of action, but unfortunately, SQL Server 2005 XQuery does not support these :) Rather, you need to get the date/time values from a T-SQL expression and pass these in to your XQuery using the sql:variable() function. There are a couple of caveats to this though - the first being that the SQL datetime and smalldatetime types are not directly supported inside XQuery expressions, so you need to convert these values into string representations before passing them in. Since there are some differences between the lexical representations of a datetime value in SQL and an equivalent value in XQuery, there is also a little string manipulation required. The following is a piece of T-SQL that will do the required conversion and then run an XQuery cast as over the resultant string to strongly type it as an xs:dateTime value:

 
declare @currDateTimeString as nvarchar(30)
set @currDateTimeString = convert( nvarchar(30), getdate(), 126) + 'Z'

declare @x xml
set @x=''
select @x.query('
sql:variable("@currDateTimeString") cast as xs:dateTime ?
')

In order to convert the SQL datetime value into the lexical representation of an xsd:dateTime value, you need to use SQL convert option 126 (for ISO8601 date format). Additionally, XQuery inside SQL Server 2005 does not support the implicit timezone, so you need to add an explicit timezone to the end of your serialized value in order to get it to parse. In this case I simply chose the UTC timezone 'Z'.

Lastly, for those paying attention, you may have noticed that the target of the cast expression is an optional xs:dateTime type. In the SQL Server 2005 XQuery implementation, it is required that all target types for cast expressions be optional. This is to handle the static typing requirement that we know the exact type at parse time, yet still be able to allow for the fact that the cast expression may fail at runtime due to an invalid lexical representation of the target type. In the SQL Server 2005 XQuery implementation, runtime errors of this sort are mapped to return the empty sequence, thus we require the optional type since any cast expression can return the requested type, or the empty sequence if the cast fails at runtime.

-
Disclaimer:
This posting is provided “AS IS” with no waranties, and confers no rights.
Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.

Comments

• Anonymous
  April 13, 2005
  I've been working on a project that uses Microsoft's SQL Server 2005. I decided that using the newly enhanced XML datatype made sense for my application, so I started by using a query like so: update kn_imchats set xmlcol.modify ('insert...

• Anonymous
  April 13, 2005
  I've been working on a project that uses Microsoft's SQL Server 2005. I decided that using the newly enhanced XML datatype made sense for my application, so I started by using a query like so: update kn_imchats set xmlcol.modify ('insert...

• Anonymous
  April 13, 2005
  Note that you can also write the equivalent but shorter expression xs:dateTime(sql:variable("@currDateTimeString")). And the reason that we need to allow for empty is because if the cast expression fails it return empty instead of raising the dynamic error.

• Anonymous
  January 17, 2009
  PingBack from http://www.hilpers.fr/335776-sql-variable-et-datetime

• Anonymous
  January 18, 2009
  PingBack from http://www.keyongtech.com/2256941-xquery-and-datetime

• Anonymous
  June 01, 2009
  PingBack from http://paidsurveyshub.info/story.php?id=74446

• Anonymous
  June 16, 2009
  PingBack from http://topalternativedating.info/story.php?id=12176

• Anonymous
  June 19, 2009
  PingBack from http://edebtsettlementprogram.info/story.php?id=24464

• Anonymous
  June 15, 2013
  Nice little work with Datetime in Xquery :)