Date Time conversion

[Alteryx_Geek]

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

[Amol_Telore]

Hey @Alteryx_Geek 

Here is my solution. 

1. Take out second and mili-second part from string.
2. Round Second number to the nearest integer.
3. Merge second part with remaining date time string.
4. convert string to date time using date time parse function.

Regards,

Amol Telore

[binuacs]

@Alteryx_Geek one way of doing this

[Alteryx_Geek]

@binuacs 

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]

@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

[Amol_Telore]

@Alteryx_Geek Could you share some sample data where you are getting this error. It will help us to identify the issue. 

[Alteryx_Geek]

@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

[binuacs]

@Alteryx_Geek 1900-01-00 is not a valid date. Do you want to keep the same date with the time?

[Alteryx_Geek]

@binuacs - the date format got messed up while copy pasting, you can take it as 2021-05-12

[OllieClarke · Accepted solution]

@Alteryx_Geek 

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