Date Format

[upaliwije]

Hi

In my data table the field TRN_DATE has data in the following format

42015

42009

42006

and I want to convert the date into following format when loading data

Date(MonthEnd(date#([TRN_DATE],'DD-MMM-YYYY')))    AS [TRN_DATE]

But I d not get desired output but Null value is returned. Pls help me to correct above script

[Anonymous]

or simply:

Date([TRN_DATE],'DD-MMM-YYYY')    AS [TRN_DATE]

[tresesco]

You are calling a function to do something (here just formatting) - it must take some system resource to do (process) 'that something'. It could be negligible, but it surely does.

[sunny_talwar]

I think he wants MonthEnd of TRN_DATE Balraj. A simple date function won't give him that.

[Anonymous]

As per his/her description:

and I want to convert the date into following format when loading data

I thought he/she just want to format in into 'DD-MMM-YYYY' only...

becuase storing monthend intead of tranxn date, would change the meaning of trxn date

[PrashantSangle]

Hi,

Yes, evaluation on number filed is much efficient than evaluation on date field.

therefore We suggest above solution.

Regards

[sunny_talwar]

So Max you are suggesting that I should be doing something like this in the future?

Date(MonthEnd(Num(DateField))) as DateField

where date field is read as date in MM/DD/YYYY format.

[roger_stone]

It might, but maybe all he wants to do is lump all the months transactions together to get an invoicing date at the end of the month.

[Anonymous]

yep, it all depends what UPALI is looking for

[PrashantSangle]

Hi,

Yes.

Regards

[upaliwije]

Thanks all for Valuable inputs