topic Re: convert integer column into date in QlikView

convert integer column into date

sunil1989 — Wed, 25 Nov 2020 16:16:04 GMT

Thanks in advance

Hi ,

i am new to qlik.

having column name DAY_KEY in integer format (20161227) want to convert in date format.

can anyone suggest solution.

Re: convert integer column into date

datagrrl — Thu, 23 Feb 2017 16:21:11 GMT

=date(date#(DateField,'yyyymmdd'),'yyyy-mm-dd')

Re: convert integer column into date

MarcoWedel — Thu, 23 Feb 2017 23:53:52 GMT

Hi,

you could try with

Date(Date#(DAY_KEY,'YYYYMMDD'))

to convert into your default DateFormat, or

Date(Date#(DAY_KEY,'YYYYMMDD'), 'MM/DD/YYYY')

to get another format as required.

Pay attention to use upper case MM instead of mm in this case as the latter means minutes instead of months. Although looking the same, the result might be wrong (different underlying numerical values).

hope this helps

regards

Marco

Re: convert integer column into date

ramasaisaksoft — Fri, 24 Feb 2017 05:56:00 GMT

Hi,

Date#() will convert your integer format to Date format.

Ex:-

The Date# function formats any given string which holds a date to a valid date format

e.g. Date#('2015-08-18','YYYY-MM-DD')

The Date() formats a valid date field to your liking

date('2015-08-18','MM/YY') will Show 08/15

if you got the solution for your issue,please close the thread by clicking "Correct Answer"

Re: convert integer column into date

priyasawant — Fri, 24 Feb 2017 06:13:49 GMT

The examples below assume the two following operating system settings:

	Default setting 1	Default setting 2
Date format	YY-MM-DD	M/D/YY

date( A ) where A=35648 returns:

	Setting 1	Setting 2
String	97-08-06	8/6/97
Number	35648	35648

date( A, 'YY.MM.DD' ) where A=35648 returns:

	Setting 1	Setting 2
String	97-08-06	97-08-06
Number	35648	35648

date( A, 'DD.MM.YY' ) where A=35648.375 returns:

	Setting 1	Setting 2
String	06.08.1997	06.08.1997
Number	35648.375	35648.375

date( A, 'YY.MM.DD' ) where A=8/6/97 returns:

	Setting 1	Setting 2
String	NULL(nothing)	97.08.06
Number	NULL	35648

Re: convert integer column into date

surendraj — Fri, 24 Feb 2017 07:08:51 GMT

date(date#(DateField,'yyyymmdd'),'MM-DD-YYYY')

Date is formatting function.

Date#() is used how to interpret the data.

By this function we need to tell to qlikview how your date format is...('yyyymmdd')

By Date function we convert this .'yyyymmdd' into 'MM-DD-YYYY'

so result will be 12-27-2017(dec 12th 2017)

Re: convert integer column into date

muthukumar77 — Fri, 24 Feb 2017 12:14:12 GMT

Hi,

try this,

=Date(MakeDate(Mid('20161227',1,4),Mid('20161227',5,2),Mid('20161227',8,2)),'DD-MMM-YYYY')

Re: convert integer column into date

MarcoWedel — Fri, 24 Feb 2017 23:04:08 GMT

please close your thread if your question is answered

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco

Re: convert integer column into date

everest226 — Fri, 24 Feb 2017 23:59:10 GMT

date(Date#(DAY_KEY,'YYYYMMDD'), 'MM/DD/YYYY')

Re: convert integer column into date

sumitjadhav — Mon, 27 Feb 2017 08:34:26 GMT

Try this Expreesion inn your script:

Date(DAY_KEY,'DD-MM-YYYY') as Datekey