How do I achieve the below output for Date Time format
2021-05-12 14:07:04.441932 = 14:07:04 (when decimal is < 0.5 msec then same digit in second)
2021-05-12 14:07:04.847982 = 14:07:05 (When decimal is >= 0.5 msec then next digit in second
Thanks
Solved! Go to Solution.
Hey @Alteryx_Geek
Here is my solution.
Regards,
Amol Telore
ConvError: Formula (67): DATETIMEPARSE: Cannot convert "2021-05-12 14:07:60" to a date/time with format "%Y-%m-%d %H:%M:%S": Second is out of range 0..59: '60'
ConvError: Formula (67): DATETIMEPARSE: Cannot convert "2021-05-12 14:07:60" to a date/time with format "%Y-%m-%d %H:%M:%S": Second is out of range 0..59: '60'
looks like its working, but then in above scenarios its not working as expected. Instead of giving 14:08:00, it gives as 14:07:60
ConvError: Formula (67): DATETIMEPARSE: Cannot convert "2021-05-12 14:07:60" to a date/time with format "%Y-%m-%d %H:%M:%S": Second is out of range 0..59: '60'
ConvError: Formula (67): DATETIMEPARSE: Cannot convert "2021-05-12 14:07:60" to a date/time with format "%Y-%m-%d %H:%M:%S": Second is out of range 0..59: '60'
looks like its working, but then in above scenarios its not working as expected. Instead of giving 14:08:00, it gives as 14:07:60
@Alteryx_Geek Could you share some sample data where you are getting this error. It will help us to identify the issue.
@Amol_Telore - Sure, below is the sample. Highlight orange cells should not change to 14:07:60, but instead it should be 14:08:00
@Alteryx_Geek 1900-01-00 is not a valid date. Do you want to keep the same date with the time?
@binuacs - the date format got messed up while copy pasting, you can take it as 2021-05-12
Try this formula
IF tonumber(Substring([Input],19))>=0.5 THEN DATETIMEADD(LEFT([Input],19),1,'second')
ELSE LEFT([Input],19)
ENDIF
Hope that helps,
Ollie