How to get datetimediff with milli seconds in Alteryx

[bdpowell]

Hello!

I have a workflow that begins by running a query. This query outputs the date as a string(so alteryx won't read it as a date and chop the milliseconds off) with milliseconds like 2021-08-05 06:10:25.9192601. As I am sure most of you know, Alteryx sets the DateTime format to 19 length. So this would change that string to 2021-08-05 06:10:25. When the datetimediff is done in SQL it calculates it with the whole string and gives the most accurate output. I cannot include this into the query within alteryx because I do multiple filters with different datetimediff calculations after the import.

I have to calculate the time in seconds between the Max and Min of the date field. This is not a huge problem on the surface when Alteryx does the datediff calculation. The difference is usually only a few seconds. However, when this workflow runs with the normal amount of data, these differences add up. This is an issue because we are trying to scale a query and automate it within alteryx. 

With all of that said, is there a way that I can convert the string with Milliseconds(2021-08-05 06:10:25.9192601) into a date format for a datetimediff calculation, or is there another way where I can do the difference of the dates that are in string format? At the very least is there a way to "Round" the string up to be 2021-08-05 06:10:26?

Thank you for the help!

[JagdeeshN · Accepted solution]

Hi @bdpowell ,

You can possibly try to read the milliseconds as a separate column.. For example if coming from SQL Server use the DatePart function to extract the millisecond:

SELECT TOP 1 SYSDATETIME() as sysTime, DATEPART(ms, SYSDATETIME()) as milliSecond from DiaryEvents

Although now you wont be able to use any of the built in date functions (for milliseconds), you can still logically implement the difference calculation manually.

Hope this helps.

Best,

Jagdeesh

[HomesickSurfer · Accepted solution]

Hi @bdpowell 

See below and attached.

If it works for you, please share, bookmark, 'Like' and/or 'Accept as Solution".  Thank you

[bdpowell]

Thank you both! Both of these options will help me fix my issue. I truly appreciate the help!