topic Re: date in QlikView

date

Tue, 22 Apr 2014 09:23:04 GMT

Hello all,

i want to remove timestamp form date field as shown in an image and convert it into proper date format ,how can i achieve this ??

Re: date

Roop — Tue, 22 Apr 2014 09:29:41 GMT

There seems to be a problem with the image, but you use the date# function to convert into a date and the date function to change the format.

Hope this helps

Re: date

its_anandrjs — Tue, 22 Apr 2014 09:29:55 GMT

Write like

Date(Date#(UploadDate,'MMM DD YYYY hh:mmTT'),'DD/MM/YYYY')

Re: date

Gysbert_Wassenaar — Tue, 22 Apr 2014 09:30:13 GMT

The best way is to do this in the script:

load

...some fields...,

date(floor(UploadDate),'DD-MM-YYYY') as MyDate

from ...somewhere...;

Replace DD-MM-YYYY with the date format you really want.

edit: it looks like your timestamp is actually a string value. In that case try date#(left(UploadDate,11),'DD-MM-YYYY')

Re: date

Tue, 22 Apr 2014 09:32:33 GMT

try Date(now(),'MMM DD YYYY')

Re: date

its_anandrjs — Tue, 22 Apr 2014 09:37:07 GMT

Load like below script

LOAD *,Date(Date#(UploadDate,'MMM DD YYYY hh:mmTT'),'DD/MM/YYYY') as Newdate;

LOAD * Inline

[

UploadDate

Aug 16 2013 3:56PM

Dec 12 2013 6:08PM

Dec 24 2013 9:07PM

Dec 28 2013 5:24PM

];

Re: date

Tue, 22 Apr 2014 09:59:41 GMT

Hi rohit,

use Date() and Date#()

Date(Date#(UploadDate,'MMM DD YYYY hh:mmTT'),'DD-MM-YYYY') as NewDate

Re: date

arjunkrishnan — Tue, 22 Apr 2014 10:03:03 GMT

Hai Frd This One Usuful For u...

SET DateFormat='DD/MMM/YYYY';

SET TimestampFormat='DD/MMM/YYYY hh:mm:ss[.fff]';

U May U Want See Only The Date Means

DATE([DateField],'$(DateFormat)') as NewFieldName,

O/P Like That

U May U Want See Only The Date & Timestamp Means

DATE([DateField],'$(TimestampFormat)') as NewFieldName,

O/P Like That

Re: date

its_anandrjs — Tue, 22 Apr 2014 10:07:06 GMT

When using below script

LOAD *,Date(Date#(UploadDate,'MMM DD YYYY hh:mmTT'),'DD/MM/YYYY') as Newdate;

LOAD * Inline

[

UploadDate

Aug 16 2013 3:56PM

Dec 12 2013 6:08PM

Dec 24 2013 9:07PM

Dec 28 2013 5:24PM

];

You get change in date column like this

Re: date

Tue, 22 Apr 2014 10:08:57 GMT

thanks anand ,but its only working for some dates for the rest its displaying null values

Re: date

SunilChauhan — Tue, 22 Apr 2014 10:24:18 GMT

can you attached your sample file

Re: date

Tue, 22 Apr 2014 10:56:27 GMT

hi,

if my date is in MMM D YYYY h:mmTT format then it will not work.'

date field is combination of

MMM DD YYYY hh:mmTT

MMM D YYYY hh:mmTT

MMM DD YYYY h:mmTT

MMM D YYYY h:mmTT

formats

Aug 16 2013 3:56PM

Jan 2 2014 12:00AM

Jan 15 2014 12:48PM

Jan 17 2014 2:32PM

Jan 28 2014 4:22PM

Jul 31 2013 1:33PM

Jun 11 2013 5:45PM

Jun 15 2013 11:10AM

Mar 4 2014 7:06PM

Mar 4 2014 7:07PM

Nov 9 2013 12:41PM

Nov 22 2013 12:00AM

Sep 14 2013 1:35PM

Sep 30 2013 4:04PM

Re: date

its_anandrjs — Tue, 22 Apr 2014 11:01:05 GMT

Hi,

It is working from my side look a script use this script

Date(Date#(UploadDate,'MMM DD YYYY hh:mmTT'),'DD/MM/YYYY') as Newdate;

//Load script

LOAD *,Date(Date#(UploadDate,'MMM DD YYYY hh:mmTT'),'DD/MM/YYYY') as Newdate;

LOAD * Inline

[

UploadDate

Aug 16 2013 3:56PM

Dec 12 2013 6:08PM

Dec 24 2013 9:07PM

Dec 28 2013 5:24PM

];

If you have sample file so provide to me

Thanks

Re: date

vardhancse — Tue, 22 Apr 2014 11:11:18 GMT

Hi,

you can use DATE(FLOOR(UPLOADDATE)) AS Upload_Date

Re: date

Tue, 22 Apr 2014 11:30:23 GMT

Thank you this is what i was looking for , ya actually timestamp is string value .

Re: date

Tue, 22 Apr 2014 11:30:48 GMT

Thank you all for responding

Re: date

arjunkrishnan — Tue, 22 Apr 2014 11:45:22 GMT

TABLE1:

LOAD * INLINE [

DATE

Aug 16 2013 3:56 PM

Jan 2 2014 12:00 AM

Jan 15 2014 12:48 PM

Jan 17 2014 2:32 PM

Jan 28 2014 4:22 PM

Jul 31 2013 1:33 PM

Jun 11 2013 5:45 PM

Jun 15 2013 11:10 AM

Mar 4 2014 7:06 PM

Mar 4 2014 7:07 PM

Nov 9 2013 12:41 PM

Nov 22 2013 12:00 AM

Sep 14 2013 1:35 PM

Sep 30 2013 4:04 PM

];

T2:

LOAD *,

Date(Date#(DATE,'MMM DD YYYY hh:mm TT'),'DD/MM/YYYY') as Newdate

Resident TABLE1;

DROP Table TABLE1;