Converting a String Timestamp to Date and Time fields

[triekong]

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!

[Anonymous]

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

[triekong]

@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!

[MindaugasBacius]

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/InterpretationFunction...

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

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

There are the result:

[jonathandienst]

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')