Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
aniruddhyadutta
Contributor

Coversion to date from Date time field

Hi All,

I want to convert the below date time field to date

datetime.PNG

it should give me as 10/21/2016 or 21-Oct-2016

I tried in my expression

date((left(DATETIME,8)),'MM/DD/YYYY')

dayname(DATETIME)

but they don't seem to work.

1 Solution

Accepted Solutions
ift_isabelle
Contributor II

Re: Coversion to date from Date time field

Try:

Date(Makedate(mid(DATETIME,5,4), Left(DATETIME,2), mid(DATETIME,3,2)), 'MM/DD/YYYY')

5 Replies
ift_isabelle
Contributor II

Re: Coversion to date from Date time field

Try:

Date(Makedate(mid(DATETIME,5,4), Left(DATETIME,2), mid(DATETIME,3,2)), 'MM/DD/YYYY')

MVP
MVP

Re: Coversion to date from Date time field

Try like:

Date(Date#(left(trim(DATETIME),8),'MMDDYYYY'))


Date#() - evaluates the expr. as a date according to the string given as format-code. I.e. it parses the string.

Date() - formats the date in given format. If the format code is omitted, the default date format set in the operating system is used.

Re: Coversion to date from Date time field

Try this way also

Data:
LOAD * Inline
[
DateSource
1021201617,3418
]
;

Step1:
Load
*,
Date(Date#(Left(DateSource,8),'MMDDYYYY'),'MMDDYYYY') as NewDate
Resident Data;
DROP Table Data;

Step2:
LOAD *,
DATE(MakeDate(Year(NewDate),Month(NewDate),Day(NewDate)),'MM/DD/YYYY') as FinalDate1,
DATE(MakeDate(Year(NewDate),Month(NewDate),Day(NewDate)),'DD-MMM-YYYY') as FinalDate2

Resident Step1;
DROP Table Step1;




Regards,

Anand

aniruddhyadutta
Contributor

Re: Coversion to date from Date time field

Thanks Isabella for your suggestion!!

aniruddhyadutta
Contributor

Re: Coversion to date from Date time field

it worked!! thanks a ton