Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Ok, I give up. I've searched and attempted many different options for resolving my date/timestamp issue without success. I have the following value:
2018-08-31T19:14:48.651-05:00
Upon extracting the date value, no matter what syntax I have employed (including the Date, Date#, Timestamp, Floor, etc. functions in various syntax positions), the date is returned as Sep 1 instead of Aug 31. What I know about this field is that it is supposed to contain the date and time CST. I would greatly appreciate assistance with the following questions.
1. How best can I most efficiently extract the date 08/31/2018 as [Trans Date]?
2. How best can I most efficiently extract the seconds, 48, as [Trans Seconds]?
As always, I appreciate your assistance!
Ellen
Hi Ellen,
Please try floor function for your first question, also see if below thread -
https://community.qlik.com/thread/252288
Regards,
Rahul
Thank you Thakkar. I previously tried the FLOOR function and read the referenced thread. I've narrowed down the issue to this part of the format:
2018-08-31T19:14:48 [.651-05:00]
In my SET TimeStampFormat statement,
Not sure how to ignore the -05:00 in the field or subtract 5 hours from the result.
The number 651 represents fractional seconds. And to get the correct date can you try to use function ConvertToLocalTime and apply floor function on top of it.
More details -
https://apex.oracle.com/pls/apex//apexcommunity/tipp/6381/index-en.html
Regards,
Rahul
When a time offset is detected, the resulting time is UTC time.
http://qlikviewcookbook.com/2016/06/qv12-timestamp-parsing/
In your example, the 5 hours are added to derive UTC time which makes it the next day. You can convert using the ConvertTolocalTime() function as Rahul suggested. Or if you really only want the date portion, use just the date portion of the string when you load it.
Date(Date#(left(myfield, 10), 'YYYY-MM-DD')) as TranDate
-Rob