Hi
I have using the Agent.SqlTable macro against the Autotask datawarehouse SQL server and one of the fields in a DateTime format of “2025-04-23 16:30:00.000”.
I need the time only and have used both CAST and CONVERT functions to achieve this in SQL, however when RM processes it, it changes it just to Date format. This happens in both Report Studio and in a schedule.
Is there an RM way to fix this?
David
One way would be to use format in the DateTime macro
[DateTime: value=2025-04-23 16:30:00.000, format=HH:mm:ss, =>Time]
Hi @Denise
Sorry if I wasn’t clear or if I am misunderstanding your suggestion.
The SqlTable query is bringing back the various date/time stamps, but when its being presented by RM it reverting this to date only. It seems to be losing all the time element so even if I change the format in Excel it only has the data and then the time is 00:00:00
I am not using a RepeatRow macro where I can understand the use of the datetime macro.
Oh, OK.
the default datatime format is yyyy-MM-dd
so add
dateTimeFormat=yyyy-MM-dd HH:mm:ss
to the Agent.SqlTable macro
e.g.
[Agent.SqlTable:
sql=“SELECT * FROM my_Table”,
worksheetName=AllData,
dateTimeFormat=yyyy-MM-dd HH:mm:ss
]
you can also do
dateTimeFormat=HH:mm
Thanks, thats exactly what I needed. I have been able to use that in conjunction with another field where I was just extracting the date fromt he same column.