Loading problem with Date which is in YYMMDD

[#1]

Hi

I am trying to load data which is a numeric field in YYMMDD format and convert it to YYYYMMDD format

I have tried the following in the script:

Date(

makedate('20'&left([Creation Date],2),mid([Creation Date],3,2), right([Creation Date],2)),'YYYY/MM/DD') as CDate

,

Creation Date is 6 digits (YYMMDD) but for year less then 10 there is no leading zero.

For Eg 51205 which represents date 5th Dec 2005 CDate is converted to -

For Eg 60103 which represents date 3rd Jan 2006 CDate is converted to 2060/10/03

Can someone please help me solve the problem so I can get correct dates.

Thanks

[er_mohit · Accepted solution]

Try This

date( date#('20' &[Creation Date],'YYYYMMD'),'YYYY/MM/DD')

as example this  copy and paste this code 

LOAD date( date#('20' &[Creation date],'YYYYMMD'),'YYYY/MM/DD')  as create,* INLINE [

    Creation date

    60103

    05024

];

[#3]

Hi ,

    Please try the below code .

=date( date#( num( '60501','000000') ,'YYMMDD'),'YYYYMMDD')

Please let me know for the same .

Thanks

[kaushiknsolanki]

Try this.

Load

Date(Date#(if(Len([Creation Date]) = 5,'0'&[Creation Date],[Creation Date]),'YYMMDD'),'DD/MM/YYYY') as New_Date inline [
Creation Date
51205
60103
091010
]

;

Regards,

Kaushik Solanki

[#5]

Hi

That solved my problem.

Thanks

[#6]

Hi ,

    Please try the below code .

=date( date#( num( '60501','000000') ,'YYMMDD'),'YYYYMMDD')

Please let me know for the same .

Thanks