topic Re: Splunk DB Connect: How to resolve error "Conversion failed when converting date and/or time from character string" for my SQL query? in All Apps and Add-ons

Splunk DB Connect: How to resolve error "Conversion failed when converting date and/or time from character string" for my SQL query?

season88481 — Fri, 28 Oct 2016 04:24:24 GMT

Hi guys,

Any one has experienced this in ingesting IBM IPS Database?

I tried to write a SQL query to return some events from the SensorDataAVP1 table. But the time format is actually a string value like this: 2015-05-09T16:08:47+1200.

I did some Googling, it is ISO8601 format with time zone Z (aka 127). But whatever I try, I always get this error "Conversion failed when converting date and/or time from character string".

The original string is in nvarchar format. So I converted it to varchar format first before converting it to datetime.

And seems the SQL datetime format doesn't support timezone, so I use datetimeoffset instead.

Here is my convert string:

convert(datetimeoffset, convert(varchar(40),time),127) AS time

Any thoughts?

Thanks

Re: Splunk DB Connect: How to resolve error "Conversion failed when converting date and/or time from character string" for my SQL query?

season88481 — Fri, 28 Oct 2016 04:28:02 GMT

And I've tried replicating the conversion on my SSMS, which works perfectly:

declare @c nvarchar(50) = '2015-02-28T01:38:28+13:00'
select convert(datetimeoffset,convert(varchar(50),@c,127),127)

Re: Splunk DB Connect: How to resolve error "Conversion failed when converting date and/or time from character string" for my SQL query?

Richfez — Fri, 28 Oct 2016 15:45:13 GMT

But wait, your example here isn't the same example as what you gave.

This example appears compliant (assuming SQL 2012ish, which may or may not be right) with it's convert requirements, specifically that it has "+13:00" whereas your original example doesn't have the colon and is "+1200".

This is probably fixable, but first could you confirm one wasn't a typo?

(Fixable - I'm not sure the best method. You could potentially manually substr all the bits and pieces into a new datetime, or it may be easier to substring out the first 90%, concatenate a colon into it and tack on the final two digits as string manipulation and THEN do your convert).

Re: Splunk DB Connect: How to resolve error "Conversion failed when converting date and/or time from character string" for my SQL query?

season88481 — Sun, 30 Oct 2016 20:54:53 GMT

Hi Rich,

Thanks for your reminder. I did make a mistake there. The timezone part of the original string is something like '+1300' not '+13:00'

Do you think I can convert the original string to a format like '2015-02-28T01:38:28+13:00', then use the same SQL query to fetch events?

Thanks a lot for your reply. Much appreciated!

Re: Splunk DB Connect: How to resolve error "Conversion failed when converting date and/or time from character string" for my SQL query?

season88481 — Tue, 29 Sep 2020 11:41:48 GMT

Hi guys,

I've gave up converting time by using SQL query. Cause I am not a sql master, so it would be a waste of time to research a solution for that.

So firstly in the sql query, I renamed the time column as fieldname 'time'.

Then ingested the time in a string format directly to Splunk. Then in the props.conf, I used a little of regex to solve the problem.

Here is the sample of my props.conf stanza:
TIME_PREFIX = time="
TIME_FORMAT = %Y-%m-%dT%H:%M:%S