Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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