topic Re: Convert Time Stamp to date only in QlikView

Convert Time Stamp to date only

Anonymous — Wed, 09 Mar 2016 18:17:57 GMT

how to convert timestamp into date format

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

to be 1/20/2015da

Re: Convert Time Stamp to date only

Gysbert_Wassenaar — Wed, 09 Mar 2016 18:24:54 GMT

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

Re: Convert Time Stamp to date only

swuehl — Wed, 09 Mar 2016 18:25:54 GMT

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

(What does 'da' mean?)

Re: Convert Time Stamp to date only

manojkulkarni — Wed, 09 Mar 2016 18:34:31 GMT

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

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

Re: Convert Time Stamp to date only

Anonymous — Wed, 09 Mar 2016 18:35:10 GMT

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

Re: Convert Time Stamp to date only

Anonymous — Wed, 09 Mar 2016 19:58:42 GMT

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

sunny_talwar — Wed, 09 Mar 2016 20:01:12 GMT

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

Re: Convert Time Stamp to date only

swuehl — Wed, 09 Mar 2016 20:20:01 GMT

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

Re: Convert Time Stamp to date only

iahamedabdullah — Wed, 09 Mar 2016 20:32:48 GMT

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

Re: Convert Time Stamp to date only

Anonymous — Wed, 09 Mar 2016 22:02:51 GMT

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.

Re: Convert Time Stamp to date only

Anonymous — Wed, 09 Mar 2016 22:05:27 GMT

Thank you Stefan for the explanation. appreciate very much