Date Interpret and Format Issue

[#1]

Hi,

      I have an issue in formatting dates as described below. Would appreciate any help on this from my fellow members.

My script has the following:

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

DateTable:

LOAD DateFormat as OriginalDateFormat,

    alt(Date#(DateFormat,'mm/dd/yyyy'),Date#(DateFormat,'dd/mm/yyyy'),Date#(DateFormat,'mm-dd-yyyy'),Date#(DateFormat,'dd-mm-yyyy')) as FormattedDate,

    alt(Date(Date#(DateFormat,'mm/dd/yyyy'),'mm.dd.yyyy'),Date(Date#(DateFormat,'dd/mm/yyyy'),'mm.dd.yyyy'),Date(Date#(DateFormat,'mm-dd-yyyy'),'mm:dd:yyyy'),Date(Date#(DateFormat,'dd-mm-yyyy'),'mm:dd:yyyy')) as FormattedDateDisplay,

    Year(alt(Date#(DateFormat,'mm/dd/yyyy'),Date#(DateFormat,'dd/mm/yyyy'),Date#(DateFormat,'mm-dd-yyyy'),Date#(DateFormat,'dd-mm-yyyy'),Date#(DateFormat,'dd-mm-yyyy'))) as FormattedDateYear

INLINE [

DateFormat

08/28/2013

28/08/2012

08-28-2014

28-08-2011];

Now my question is regarding the field: FormattedDateDisplay which shows as below

Now for 28-08-2011 I have used Date(Date#(DateFormat,'dd-mm-yyyy'),'mm:dd:yyyy') still getting the display as 28:08:2011 instead of 08:28:2011.

Thanks,

Shoubhik.

[anbu1984 · Accepted solution]

Try

alt(Date(Date#(DateFormat,'MM/DD/YYYY'),'MM.DD.YYYY'),Date(Date#(DateFormat,'DD/MM/YYYY'),'MM.DD.YYYY'),Date(Date#(DateFormat,'MM-DD-YYYY'),'MM:DD:YYYY'),

    Date(Date#(DateFormat,'DD-MM-YYYY'),'MM:DD:YYYY')) as FormatteDDateDisplay_NEW

[#3]

This works...Thanks a lot. Well since date formats are case-sensitive so MM and mm interpretations are different.