topic Re: Time to Date in QlikView

Time to Date

balasundaram — Sun, 14 Sep 2014 17:01:39 GMT

i want to convert 15-01-2014 12:00:00 to 1/15/2014

how ??

Re: Time to Date

nagaiank — Sun, 14 Sep 2014 17:06:06 GMT

Try

=Date(Floor(Date#('15-01-2014 12:00:00','DD-MM-YYYY hh:mm:ss')),'M/D/YYYY')

Re: Time to Date

maxgro — Sun, 14 Sep 2014 17:18:08 GMT

=date(date#('15-01-2014 12:00:00', 'DD-MM-YYYY hh:mm:ss'),'M/DD/YYYY')

EDIT you can read this for detailed help

Primer for QlikView Date fields

Re: Time to Date

sundarakumar — Sun, 14 Sep 2014 17:21:45 GMT

=date(date#('15-01-2014 12:12:2014','DD-MM-YYYY hh:mm:ss'))

this will do. if u convert it to number u will see the corresponding time fraction. with this u can change it back to timestamp at any point..

-Sundar

Re: Time to Date

Sun, 14 Sep 2014 17:41:52 GMT

try this

date(date#('15-01-2014 12:00:00','DD-MM-YYYY hh:mm:ss'),'MM/DD/YYYY')

Re: Time to Date

MarcoWedel — Sun, 14 Sep 2014 20:05:43 GMT

Hi,

supposed your timestamp values are stored in a field 'yourField',then

if yourField is type text:

date(timestamp#(yourField, 'DD-MM-YYYY hh:mm:ss'), 'M/DD/YYYY')

if yourField is type Timestamp:

date(yourField, 'M/DD/YYYY')

Both methods leave the time part of yourField untouched, i.e. whenever you use the result as a dimension, you probably get multiple occurrences of 1/15/2014 (each with a different, unvisible time value).

So if you intend to get numerically identical values per date (i.e. dates not timestamps), you have to strip the time part like:

if yourField is type text:

date(floor(timestamp#(yourField, 'DD-MM-YYYY hh:mm:ss')), 'M/DD/YYYY')

date(daystart(timestamp#(yourField, 'DD-MM-YYYY hh:mm:ss')), 'M/DD/YYYY')

date(dayname(timestamp#(yourField, 'DD-MM-YYYY hh:mm:ss')), 'M/DD/YYYY')

if yourField is type Timestamp:

date(floor(yourField), 'M/DD/YYYY')

date(daystart(yourField), 'M/DD/YYYY')

date(dayname(yourField), 'M/DD/YYYY')

hope this helps

regards

Marco