Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
triekong
Contributor

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!
Capture2.PNG

4 Replies
Partner
Partner

Re: Converting a String Timestamp to Date and Time fields

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
Contributor

Re: Converting a String Timestamp to Date and Time fields

@hrlinder 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
Valued Contributor III

Re: Converting a String Timestamp to Date and Time fields

MVP
MVP

Re: Converting a String Timestamp to Date and Time fields

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