Time conversion in dbxquery

[jerinvarghese]

Hi all,

need help in converting the time format. I want to add another 10.5 hrs to the submit date.

Below query i tried and failed.

1. | eval Time=strftime(Submit_Date+10.5*3600,"%Y-%m-%d %l:%M:%S %p")
2. | fieldformat Time=strftime(Submit_Date+10.5*3600,,"%Y-%m-%d %H:%M:%S")

Complete code is below.

| dbxquery connection="ITDW" shortnames=true  query="SELECT
GETDATE() as 'CurrentTime',
[Incident_Number],
INC.[Company],
[Customer],
[Summary],
[Notes],
[Service],
[CI],
[Impact],
[Urgency],
[Priority],
[Incident_Type],
[Assigned_Support_Group],
[Assigned_Support_Organization],
[Status],
[Status_Reason],
[Resolution],
[Reported_Date],
[Responded_Date],
[Closed_Date],
[Last_Resolved_Date],
[Submit_Date],
[Last_Modified_Date],
[Owner_Group]
FROM [shared].[ITSM_INC_MAIN] INC
LEFT OUTER JOIN [shared].[ITSM_CMDB_People_Main] PPL
ON 
INC.Customer_ID = PPL.Person_ID
WHERE ([Assigned_Support_Group] = 'Ops-WAN and LAN Incidents') 
AND [Submit_Date] BETWEEN DATEADD(D,-3,GETDATE()) AND GETDATE()"
| table Customer, Incident_Number, Submit_Date

How can i edit the time in dbxquery, please help in that.

when ever I try strftime, the output is coming blank without no value.

[ITWhisperer · Accepted solution]

I haven't used dbxquery but I suspect Submit_Date is coming back as a string, so you need to parse the string into a time value

...
| eval Time=strptime(Submit_Date, "<whatever time format the query is returning")
...

You can then add the 10.5 hours to this

...
| eval Time=Time+(10.5*3600)
...

 (you might be able to put the addition at the end of the previous eval)

Then, to change the way it is displayed, use fieldformat

...
| fieldformat Time=strftime(Time,"%Y-%m-%d %H:%M:%S")
...