Date Format Changes

[gireesh1216]

How to change number format to date format?

Example Number:-693593

I am using DATE(-693593  ) function.getting 01-01-0001.

how to convert  -693593  to  01-01-2001 ????

[Miguel_Angel_Baeyens]

Hi Gireesh,

Indeed, dates cannot be negative numbers in QlikView[1]. First you will need to Fabs(Field) to remove the negative number without affecting the value and then use the Date() function.

Miguel

[1] For the sake of being as technically accurate as possible, dates can be whatever number with whatever sign, integer or with decimals, positive or negative. Noting that Day 1 for the Date() function in QlikView is 31/12/1899 (DD/MM/YYYY), any day with negative sign means it happens before that day. If that is the date coming from the source, it is likely that the field in the source is empty or the day 1 for that source system, which could be whatever value.

[gireesh1216]

Thanks Miguel...

But How to remove negative numbers in QlikView? please help me on this...

[Miguel_Angel_Baeyens]

I already told you, use the Fabs() function on the date in the script:

Date(Fabs(DateField)) AS Date

Miguel

[gireesh1216]

Hi Miguel....

I am using Date(fabs(-693593)) as Date_join.But  iam getting wrong date(12/27/3798)..

[Miguel_Angel_Baeyens]

Yes, 693593 is that future date for QlikView.

In QlikView, Day 1 is 31/12/1899, and today is day 42644, so I don't know where that date is coming from but that seems definitely wrong.

[Anil_Babu_Samineni]

Miguel, I think to day is day 42663

And Gireesh, You might have few Forecast Data for future

LOAD *, Date(fabs(Date),'DD-MM-YYYY') as Date1;

LOAD * Inline [

Date

-693593

-693543

-693234

];

Screen Shot which i got is below for Date1

[gireesh1216]

hi,

use DATE(-693593  ) function u will get 01-01-0001.

I want 0001 to 2001

[vcanale]

Hi,
from Miguel reply Date(fabs('NegativeNumb')) works correctly, but where "-693593" comes from?

01-01-2001 --> 36892
eg: Date(Fabs('-36892')) returns 01-01-2001

[gireesh1216]

In database showing data for one employee

:

After I loaded in qlikview getting below data:

USING DATE(FIELD NAME ) GETTING BELOW DATA:

finally i want  date format in 01/01/2001

Date Format Changes