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

[sunny_talwar]

Try just with date function:

Date(MonthEnd(Date([TRN_DATE))) as [TRN_DATE]

UPDATE: you would use Date#() function if TRN_DATE was actually coming as text but was in your specified format. Right now it is coming as numbers which can be easily converted into Date by using the Date() function.

[PrashantSangle]

Hi,

Date(MonthEnd(TRN_DATE)) as Month_TRN_DATE

Regards

[tresesco · Accepted solution]

Try like:

Date(MonthEnd(TRN_DATE), 'DD-MMM-YYYY') as TRN_DATE

[tresesco]

Date() (in red) is redundant here.

[PrashantSangle]

No need to use date or date# function in MonthEnd()

Regards

[sunny_talwar]

Agreed

[roger_stone]

All you need is this:

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

[sunny_talwar]

Since both of you mentioned it, do you believe that it takes extra system resources for having it there? Cause for when I code, I like putting it there to make the code more readable and understandable for myself. But if it does take system resources, I might change my habit

[nagarjuna_kotha]

Try this:

>>Date(MonthEnd(TRN_DATE), 'DD-MMM-YYYY') as TRN_DATE

-Nagarjuna