SQL Issues With DateFields When Using FOR XML (to create JSON String)

Question

Based on this answer, I have created a function to loop through column names and return a query result into JSON format. I am using FOR XML because I am working on an SQL Server version that does not support FOR JSON.

I have the query returning valid JSON format, except where there are date fields, and although I have been searching online I think I am a bit over my head in terms of depth of SQL Server understanding. I have been playing with CAST and CONVERT and ISDATE but cannot get the query to generate results. I am also a little unclear on how the value('.', 'varchar(max)') works, even though I have read up on it.

For example, this query works perfectly.

SELECT
    STUFF((
        SELECT',{"account_no":"' + account_no + '"' + ',"version_num":"' + version_num + '"' + ',"user_id":"' + user_id + '"' + '}' 
        FROM uAccountHighLevel
        WHERE account_no='3718035' and version_num='37' 
        FOR XML path(''), type).value('.', 'varchar(max)')
    , 1, 1, '')

Returning

{"account_no":"3718035","version_num":"37","user_id":"Sholtzman"}

However, the moment I add a datetime (or date) field into the query, it fails with this message:

Conversion failed when converting date and/or time from character string.

See query with datetime field added:

SELECT
    STUFF((
        SELECT',{"account_no":"' + account_no + '"' + ',"version_num":"' + version_num + '"' + ',"user_id":"' + user_id + '"' + ',"time_stamp":"' + time_stamp + '"' + '}' 
        FROM uAccountHighLevel 
        WHERE account_no='3718035' and version_num='37' 
        FOR XML path(''), type).value('.', 'varchar(max)')
    , 1, 1, '')

Here is a snapshot of the table I am working with. All the tables have similar mix of data types:

If the field values were known each time, I think I could solve it, but the function that creates this query will be re-used on many tables, so a date/datetime field can appear in any order, so I need this function to build in some way to handle datetime fields. I also have no control over changing the datatypes in the database.

Below is the vba code I use to create the JSON.

Sub querySQL()

    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    conn.Open "Provider=SQLOLEDB;Data Source=DVW-SQL02;Initial Catalog=UniversalQuoteProposal;UID=SVC_UQP;PWD=$vc13#up"

    'grab column names
    Dim sql As String
    sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'uAccountHighLevel'"

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    With rs
        .ActiveConnection = conn
        .CursorLocation = adUseClient
        .Open sql, conn, adOpenKeyset, adLockReadOnly, adCmdText
    End With

    sql = buildSQL("uAccountHighLevel", rs, "List")

    Dim rsFinal As ADODB.Recordset
    Set rsFinal = New ADODB.Recordset

    With rsFinal
        .ActiveConnection = conn
        .CursorLocation = adUseClient
        .Open sql, conn, adOpenKeyset, adLockReadOnly, adCmdText
        Debug.Print .Fields(0).Value
    End With


End Sub

Function buildSQL(theTable As String, rs As ADODB.Recordset, arrayOrList As String) As String

    'this has to be fixed, but we can do it :)
    'adjust query builder and also had a case for isdate to cast to string

    Dim queryBuilder As String
    queryBuilder = "SELECT "
    If arrayOrList = "array" Then queryBuilder = queryBuilder & "'[' + "
    queryBuilder = queryBuilder & "STUFF(("
    queryBuilder = queryBuilder & "SELECT',{"

    rs.MoveFirst
    Dim f As Long
    For f = 0 To 3 'rs.RecordCount - 1
        queryBuilder = queryBuilder & """" & rs.Fields(0).Value & """:""' + " & rs.Fields(0).Value & " + '""' + ',"
        'this is one of my attempts to play with isdate, cast, convert
        'queryBuilder = queryBuilder & """" & rs.Fields(0).Value & """:""' + (CASE WHEN ISDATE(" & rs.Fields(0).Value & ") = 1 THEN CONVERT(datetime, cast([" & rs.Fields(0) & "] AS CHAR(8))) END) + '""' + ',"
        rs.MoveNext
    Next

    queryBuilder = Left(queryBuilder, Len(queryBuilder) - 1) & "}'"

    queryBuilder = queryBuilder & " FROM " & theTable & " WHERE account_no='3718035' and version_num='37' "
    queryBuilder = queryBuilder & " for xml path(''), type"
    queryBuilder = queryBuilder & ").value('.', 'varchar(max)'), 1, 1, '')"

    If arrayOrList = "array" Then queryBuilder = queryBuilder & " + ']'"

    buildSQL = queryBuilder

End Function

Why are you using `FOR XML PATH` to create JSON and not `FOR JSON PATH`? — Thom A, Nov 07 '19 at 16:00
@Larnu - Good question. Forgot to clarify that I am not on a version of SQL Server that supports `FOR JSON PATH` — Scott Holtzman, Nov 07 '19 at 16:05

score 2 · Answer 1 · answered Nov 07 '19 at 16:10

2

The reason for the error is due to Data Type Precedence. You are trying to concatenate a datetime value to a varchar, and as datetime has the higher precedence of the 2, SQL Server attempts to implicitly convert the varchar to a datetime; which obviously errors.

You need to explicitly convert the value of the datetime to a varchar. I use the ISO format yyyyMMdd style here, but you may need to change this to a different one (and increase the size of the varchar appropriately):

SELECT STUFF((SELECT ',{"account_no":"' + account_no + '"' + ',"version_num":"' + version_num + '"' + ',"user_id":"' + user_id + '"' + ',"time_stamp":"' + CONVERT(varchar(8), time_stamp, 112) + '"' + '}'
              FROM uAccountHighLevel
              WHERE account_no = '3718035'
                AND version_num = '37'
             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'');

answered Nov 07 '19 at 16:10

Thom A

88,727
11
45
75

While, I appreciate your attempt to answer this, I mentioned in my question `... but the function that creates this query will be re-used on many tables, so a date/datetime field can appear in any order ... `. Therefore, I need a solution that will be able to inherently check each field as it's built and determine what to do with it..... And now, I see how I did not clearly explain that above. I will edit my post. – Scott Holtzman Nov 07 '19 at 16:14
So you're after a dynamic statement, @ScottHoltzman ? Then you should be looking at upgrading to a version of SQL Server that properly supports JSON or using something *else* in my view. Guessing the correct format, for multiple data types, has a huge potential to ruin your data. – Thom A Nov 07 '19 at 16:16
agreed that this isn't the ideal solution, but right now, if I can't get it working, it's the *best* solution i have at my disposal, considering the task I have and data that I am working with. – Scott Holtzman Nov 07 '19 at 16:19
Thanks for the correction :) Does SQL have some type of "CAST ANY TYPE TO CHAR" function? – Scott Holtzman Nov 07 '19 at 16:20
*"Does SQL have some type of "CAST ANY TYPE TO CHAR" function?"* Yes, it's they are called `CONVERT` and `CAST`, but the problem is you need to define the size of said `varchar`, **and** (possibly more importantly) the style. For dates, in things like JSON that can be very important. – Thom A Nov 07 '19 at 16:21
Thanks. I will play with `CAST` and `CONVERT` more. Even if the date is a string literal in JSON, I can deal with that on the other end of my solution. – Scott Holtzman Nov 07 '19 at 16:23
I figured it out, with your help, indirectly. Because I looked at `CAST` again in a new light :) So thank you. I realize it's a bit of brute force, but for now, that works for what I need. – Scott Holtzman Nov 07 '19 at 16:32

score 0 · Answer 2 · answered Nov 07 '19 at 16:35

0

I was able to get it to work by forcing everything to VARCHAR(Max).

So, the queryBuilder column looper now reads:

queryBuilder = queryBuilder & """" & rs.Fields(0).Value & """:""' + CAST(" & rs.Fields(0).Value & " AS VARCHAR(MAX)) + '""' + ',"

Which produces this:

SELECT
    STUFF((
        SELECT',{"account_no":"' + CAST(account_no AS VARCHAR(MAX) + '"' + ',"version_num":"' + CAST(version_num AS VARCHAR(MAX) + '"' + ',"user_id":"' + CAST(user_id AS VARCHAR(MAX) + '"' + ',"time_stamp":"' + CAST(time_stamp AS VARCHAR(MAX) + '"' + '}' 
        FROM uAccountHighLevel 
        WHERE account_no='3718035' and version_num='37' 
        FOR XML path(''), type).value('.', 'varchar(max)')
        , 1, 1, '')

answered Nov 07 '19 at 16:35

Scott Holtzman

27,099
5
37
72

It's `vba` not `.net` @Larnu – Scott Holtzman Nov 07 '19 at 16:38
Either way, injecting those raw values is a really bad idea. At least sanitise them. [Little Bobby Tables](https://xkcd.com/327/). In this case, you should be replacing any `]` characters with `]]` and then wrapping the entire value with `[]`. This would mean someone trying to inject `sys.objects]; SELECT * FROM sys.tables;--` would actually just have the value `[sys.objects]]; SELECT * FROM sys.tables;--]` passed to the query (which is obviously not a valid column). – Thom A Nov 07 '19 at 16:40

SQL Issues With DateFields When Using FOR XML (to create JSON String)

2 Answers2