topic Re: Converting a String Timestamp to Date and Time fields in QlikView

Converting a String Timestamp to Date and Time fields

triekong — Sat, 16 Nov 2024 20:47:09 GMT

my dates come in as "Apr 1 2008 12:00AM" and i am trying to get the date and time separate from each other.

i tried using 'subfield' and a date# function, but when the date changes to "Apr 31 2008 1:00AM", the character spaces and placement changes and it returns incorrect dates.

Goal is to get the date into 'YYYY-MM-DD' format, and the time into 'HH:MM:SS' format, as new fields, separated from each other.

let me know what i am missing. thanks!

Re: Converting a String Timestamp to Date and Time fields

Anonymous — Tue, 28 May 2019 06:34:16 GMT

this expression shows data and time in textbox
=date(date#('Apr 1 2008 12:00AM','MMM dd YYYY hh:mmTT'))
& '-' & time(date#('Apr 1 2008 12:00AM','MMM dd YYYY hh:mmTT'))

maybe you can take the relevant part for your desired expression

Re: Converting a String Timestamp to Date and Time fields

triekong — Mon, 03 Jun 2019 05:39:06 GMT

@Anonymous thanks for the solution. it does not work when inputted the field name as compared to a typed up date. i tried using the chr(39) to get the quotation marks in but that did not work.

i have attached a sample. thanks!

Re: Converting a String Timestamp to Date and Time fields

MindaugasBacius — Mon, 03 Jun 2019 05:56:09 GMT

I suggest you reading about Interpretation function Date# and about Formatting functions Date, Time:

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/InterpretationFunctions/date_hash.htm

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/FormattingFunctions/Date.htm

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/FormattingFunctions/Time.htm

There are the result:

Re: Converting a String Timestamp to Date and Time fields

jonathandienst — Mon, 03 Jun 2019 06:07:36 GMT

Be careful of using date formatting alone. the Date(Date#()) construct does not remove the time component from the date values, it simply hides them, so you can still get multiple distinct values of a single date. You need to explicitly remove the time component like this:

=Date(Floor(Timestamp#(postdateofposition, 'MMM D YYYY h:mmtt')), 'YYYY/MM/DD') =Time(Frac(Timestamp#(postdateofposition, 'MMM D YYYY h:mmtt')), 'HH:MM:SS')