CODEWITHSUNDEEP
sqljsonazure-sql-database
Manjunath Rao
Manjunath Rao

Reputation: 1511

Azure MSSQL - The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value

I have a column in Azure SQL database (MSSQL) which stores JSON information as VARCHAR. One of the field is 'date' which is a string. Now I want select rows based on some criteria. The query works for criteria like - event_type and component_type, however, it fails for the date criteria.

I want to query using inputs like start_date and end_date that will be a string/varchar and select rows that will be greater than start_date and less than end_date.

Query I am using:

SELECT event_log FROM [dbo].[tblEventStore] where JSON_VALUE(event_log, '$.instance_id') LIKE 'XTO-TEST-45' AND
JSON_VALUE(event_log, '$.event_type') LIKE 'create' AND JSON_VALUE(event_log, '$.component_type') LIKE 'webapp'
AND CAST(JSON_VALUE(event_log, '$.date') as DATETIME2) BETWEEN '2021/09/02 12:00:00' and '2021/09/02 15:00:00'

JSON:

{
    "requesting_user": "",
    "event_type": "create",
    "instance_id": "XTO-TEST-45",
    "component_type": "webapp",
    "date": "02/09/2021, 14:01:18",
    "request": {
        "webapp": "testwebappmanjug48",
        "os": "linux",
        "is_containerized_webapp": "false"
    },
    "provisioning_status": "request received"
}

Error:

Msg 241, Level 16, State 1, Line 10
Conversion failed when converting date and/or time from character string.

Another query:

SELECT CONVERT(datetime, JSON_VALUE(event_log, '$.date'), 110) FROM [dbo].[tblEventStore]

Exception:

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

Upvotes: 0

Views: 239

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

The format you are using is not a readily recognized format for convert(). So, you can convert the date and time components separately:

select convert(datetime, left(JSON_VALUE(event_log, '$.date'), 10), 103) + convert(datetime, right(JSON_VALUE(event_log, '$.date'), 8))

In general, if you want to store date/time values as a string, use ISO formats: YYYY-MM-DD or YYYY-MM-DDTHH24:MI:SS.

Upvotes: 1

Related Questions