Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

qlikdash
Valued Contributor

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
MVP & Luminary
MVP & Luminary

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
Valued Contributor

Re: Convert Time Stamp to date only

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

qlikdash
Valued Contributor

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
Valued Contributor

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.