Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
qlikdash
Contributor II

Convert Time Stamp to date only

how to convert timestamp into date format

I want this 2015-01-20L20:23:56X

to be 1/20/2015da

Tags (2)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Convert Time Stamp to date only

=Date(Left('2015-01-20L20:23:56X',10),'M/D/YYYY')

(What does 'da' mean?)

10 Replies

Re: Convert Time Stamp to date only

Try: Date(Date#(Subfield('2015-01-20L20:23:56X','L',1),'YYYY-MM-DD'),'M/D/YYYY')


talk is cheap, supply exceeds demand
Highlighted
MVP
MVP

Re: Convert Time Stamp to date only

=Date(Left('2015-01-20L20:23:56X',10),'M/D/YYYY')

(What does 'da' mean?)

manojkulkarni
Valued Contributor II

Re: Convert Time Stamp to date only

if your data is in proper datetime format, you can even do with floor function

date(floor(CREATED_DATE),'DD/MM/YYYY') as RealDate

qlikdash
Contributor II

Re: Convert Time Stamp to date only

sorry da was a typo. it sud be only until date

qlikdash
Contributor II

Re: Convert Time Stamp to date only

Gysbert,

how do I handle if field contains some thing else For example, number or any letter.

so in the ('2015-01-20L20:23:56X','L',1) how can we make it dynamic to include changes

in the same position where 'L' is.

Re: Convert Time Stamp to date only

I would just pick the first 10 characters using Left if the format is going to be YYYY-MM-DD (and not YYYY-M-D)

Date(Date#(Left('2015-01-20L20:23:56X', 10), 'YYYY-MM-DD'), 'M/D/YYYY')

MVP
MVP

Re: Convert Time Stamp to date only

QlikDash,

there are two basic approaches to interprete a timestamp or part of it as date:

Either use a single interpretation function with an appropriate format code that digest the complete timestamp string and returns a QV dual value (edit: then use numeric & QV Date functions to retrieve the date part), or use string functions like LEFT(), SUBFIELD(), etc. to chop the timestamp string into parts and pipe the parts into interpretation functions.

Latter is what Gysbert and I did, using Left() / Subfield(). Which one is better? This depends on your input string format and possible changes to the format across your records.

If the delimiter between date and time changes, use Left() as I did above, If it doesn't change or the date part changes in number of character, a Subfield() might be better.

Note that I haven't used an explicite interpretation function like Date#(), because I bet on the implicite date interpretation of a ISO date format.

Hope this helps,

Stefan

Get the Dates Right

On Format Codes for Numbers and Dates

iahamedabdullah
New Contributor II

Re: Convert Time Stamp to date only

Try this:

Option 1: date(LEFT(NUM(DATE#('2015-01-20 20:23:56','YYYY-MM-DD hh:mm:ss')),5),'MM/DD/YYYY')

Option2: If MM DD is always 2 chars, then use this DATE(LEFT('2015-01-20 20:23:56',10),'MM/DD/YYYY')

qlikdash
Contributor II

Re: Convert Time Stamp to date only

I was getting 2015-1-20 as a result. I found out that I was using Date# instead of Date which was causing so.

Thanks everyone.

Community Browser