Convert Time Stamp to date only

[Anonymous]

how to convert timestamp into date format

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

to be 1/20/2015da

[Gysbert_Wassenaar]

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

[swuehl · Accepted solution]

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

(What does 'da' mean?)

[manojkulkarni]

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

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

[Anonymous]

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

[Anonymous]

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.

[sunny_talwar]

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

[swuehl]

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]

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

[Anonymous]

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.