Odd behavior in Date Calculation

[Anonymous]

Hi,

I have a table with three columns Date, FiscalYear and MonthName(Date). Sample data:

Date   FiscalYear     MonthName(Date)

42186 2016              Jul 2015

42187 2016              Jul 2015

Once I remove the MonthName(Date) column from the table, then the data immediately looks like:

Date                                      FiscalYear   

7/1/2015 12:00:00 AM             2016.000000000000000000

7/1/2015 12:00:00 AM             2016.000000000000000000

How that MonthName(Date) influence the format of other two columns(Date and FiscalYear). Please explain

Thanks

[MK_QSL]

how you have calculated these fields?

[sunny_talwar]

Would you be able to share your script with and without MonthName(Date)

[Anonymous]

Hi Sunny,

I am pulling this from a QVD. By looking at the QVD, When I see the CALENDAR_DATE in the File Wizard, it is a number like 33585,33586 etc and Fiscal Year also a number like 1990 etc.

TempCalendar: 

LOAD

  CALENDER_DATE,

  CALENDER_YEAR AS Year,

  FISCAL_YEAR AS FiscalYear

// MonthName(CALENDER_DATE) AS [Month Year]

  FROM

  CALANDER_EXT.qvd(qvd);

If I load this QVD without the MonthName(CALENDER_DATE) AS [Month Year], then the CALENDER_DATE is getting converted to real date like 7/1/2015 12:00:00 AM and FiscalYear is getting converted to 2016.00000000000.

If I use the MonthName(CALENDER_DATE) AS [Month Year], then i am getting the same value as what i see in the file wizard of the QVD.

Thanks