Date Time conversion
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @Alteryx_Geek
Here is my solution.
- Take out second and mili-second part from string.
- Round Second number to the nearest integer.
- Merge second part with remaining date time string.
- convert string to date time using date time parse function.
Regards,
Amol Telore
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Alteryx_Geek Could you share some sample data where you are getting this error. It will help us to identify the issue.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Alteryx_Geek 1900-01-00 is not a valid date. Do you want to keep the same date with the time?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@binuacs - the date format got messed up while copy pasting, you can take it as 2021-05-12
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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