Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
Showing results for 
Search instead for 
Did you mean: 

Converting a String Timestamp to Date and Time fields

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!

4 Replies
Not applicable

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


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

Partner - Champion III
Partner - Champion III

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


Logic will get you from a to b. Imagination will take you everywhere. - A Einstein