topic Re: Date format in QlikView

Date format

Mon, 14 Sep 2015 05:42:05 GMT

Hi Guys,

I have data in my excel as 2015-07-04 00:36 format which is M/D/YYYY h:mm:ss[.fff] TT' but suddenly some data came in excel as Sep 7, 2015 5:19:42 PM format so it is not takinf date values that are in new format. if i try to convert to M/D/YYYY it takes only till old format not taking the new format. How can i handle this?

Re: Date format

Mon, 14 Sep 2015 05:45:42 GMT

Hi,

Try Using Date#() Function

Converting date format

Re: Date format

Mon, 14 Sep 2015 05:46:42 GMT

Hi,

go through this.

The Date Function

Re: Date format

sunny_talwar — Mon, 14 Sep 2015 05:47:36 GMT

May be this:

Date(Alt(Date#(DateField, 'YYYY-MM-DD hh:mm'), Date#(DateField, 'MMM D, YYYY hh:mm:ss TT'))) as DateField

Re: Date format

sasiparupudi1 — Mon, 14 Sep 2015 05:50:05 GMT

use

alt

alt(timestamp#('07/4/2015 00:36:34.000 AM','M/D/YYYY h:mm:ss[.fff] TT'),timestamp#('Sep 7,2015 5:19:42 PM','MMM DD,YYYY hh:mm:ss TT'))

hth

Sasi

Re: Date format

senpradip007 — Mon, 14 Sep 2015 05:52:06 GMT

Try like

alt(Timestamp#(Date, 'yyyy-mm-dd hh:mm'), Timestamp#(Date, 'MMM dd, yyyy h:mm:ss TT')) as NewDate

Re: Date format

Mon, 14 Sep 2015 06:58:32 GMT

can't it be handled without using alt function?

Re: Date format

sasiparupudi1 — Mon, 14 Sep 2015 07:00:35 GMT

Correct the date format in the source excel file

Re: Date format

sunny_talwar — Mon, 14 Sep 2015 07:01:25 GMT

If you don't want to use Alt, you can use If function, but Alt would be an easier route to take

Re: Date format

Mon, 14 Sep 2015 07:12:53 GMT

i mean to say, before whatever format date format data used to come in excel it used to convert to M/D/YYYY but this format it is not converting, user wants us to make it dynamic with whatever format they enter

Re: Date format

mahesh_agrawal — Mon, 14 Sep 2015 07:56:40 GMT

Hi Nilanjan,

Please find the attached app. Hope, It will help you to resolve the issue.

Regards,

Mahesh