Making Master Calender

[alkesh_sharma]

Hello,

I have a data in which the date format is as below:

1-JAN_2013

2-JAN_2013 and so on...

How can i change it in standard format as the Year(), Month() commands are not working on the mentioned format.

Thanx in Advance

[#2]

Look at your default set statements for dates

Mine are

SET DateFormat='M/D/YYYY';

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

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

To use your format change set statements to

SET DateFormat='D-MMM_YYYY';

SET TimestampFormat='D-MMM_YYYY h:mm:ss[.fff] TT';

SET MonthNames='JAN;FEB;MAR;APR;MAY;JUN;JUL;AUG;SEP;OCT;NOV;DEC';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

[MarcoWedel]

replace the original DateFormat in the load script with

SET DateFormat='D-MMM_YYYY';

regards

Marco

[MK_QSL]

Check enclosed file..

[sudeepkm]

Please take a look at the attachment.

[msteedle]

As an alternative to the formatting approach, you can convert it when loading it using the date conversion function, DATE#.

Date#([YourDateField], 'D-MMM_YYYY') as [YourDateField]

[alkesh_sharma]

Thanx for your suggestions but this approach is not working...:(

[jagan]

Hi,

Can you attach sample file that you are working with some data.

Regards,

Jagan.

[ashwanin]

Hi Akhlesh,,

Please add below in your script to make Year, Month and Day. Here i have taken Date as field where the  date format '1-JAN_2013' exists.

Left(Date,1) as Day,

right(Date,4) as Year,

Mid(Date,Index(Date,'-',1)+1,3) As Month,

[ashwanin]

and in case User has used the month field like July i/o JUL the put the below formula for Month field.

Mid(Date,Index(Date,'-',1)+1,Index(Date,'_',1)-3) As Month,