Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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';
replace the original DateFormat in the load script with
SET DateFormat='D-MMM_YYYY';
regards
Marco
Check enclosed file..
Please take a look at the attachment.
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]
Thanx for your suggestions but this approach is not working...:(
Hi,
Can you attach sample file that you are working with some data.
Regards,
Jagan.
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,
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,