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
Contributor

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

0 Karma
1 Solution

season88481
Contributor

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

View solution in original post

0 Karma

season88481
Contributor

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

View solution in original post

0 Karma

season88481
Contributor

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)

0 Karma

Richfez
SplunkTrust
SplunkTrust

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).

0 Karma

season88481
Contributor

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!

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!