Date Conversion strange behaviour

[#1]

Hi,

I'm using the script below to convert the dates in the attached excel sheet.

LOAD Name,

date(alt(date#([date] , 'YYYY/MM/DD' ),date#([date], 'MM/DD/YYYY' ),date#( [date] , 'MM/DD/YY' ),date#([date], 'DD/MM/YYYY' ),

     date#([date], 'MM/DD/YYYY'),date#([date], 'M-DD-YY' ),Date#([date], 'DD/MM/YY'),

     date#([date], 'MM-DD-YY' ), num#([date], 'M-DD-YY' ),0))  as date

FROM

test.xlsx

(ooxml, embedded labels, table is Sheet1);

Please note that although the format 'YYYY/MM/DD'  is not present in the excel sheet but still i'm using it as this format used to be there in an earleir version of the file & could be there in future also.

The problem is the conversion is correct for every name exect for name: I for which I get :

13/02/2028

13/04/2030

12/06/2030

13/01/2031

13/03/2031

My set/default date format in the file is DD/MM/YYYY.

When i remove date#([date] , 'YYYY/MM/DD' ) from the alt then it works fine.

Why is this happening ??

Regards

[kaushiknsolanki]

Hi,

     You must understand the working of Date#() function. Let me explain in detail.

     Date#(Field,Format) :

            This function is used to convert the text into Date. So suppose if I have a text 21/01/2012 then I can use the Date#() to convert this text into date format.

      Parameters:

          Field:

                 This is the name of the Text field which is coming from any of your data source.

          Format:

                  This is the format in which the text contains the date.

     Example 

                              Text :  21-03-2013

          Expression will be : Date#(Text,'DD-MM-YYYY')

                              Text :  2013/03/19

          Expression will be :  Date#(Text,'YYYY/MM/DD')

     Note : The format should be the same format in which your text is.

Regards,

Kaushik Solanki

[sujeetsingh]

It seems same viswarajan...........there is two data types in a column!!1

[#4]

it is not a good practice to touch the excel data

[#5]

Hi,

i completely know the date# working, you just need to tell me where the expression is wrong.

" When i remove date#([date] , 'YYYY/MM/DD' ) from the alt then it works fine."

why ?

Regards

[kaushiknsolanki]

HI,

     This is because the field which is coming from your excel file contains the Data in Date as well as text format.

     If you look at the excel file you will find the data

H	4/21/2013
H	5/30/2013
H	5/25/2013
I	30/06/12
I	31/01/13
I	31/01/13

        You can see here that the value is in Date format for H but for I it is not.

Regards,

Kaushik Solanki      

[#7]

Yes, i know, but how does the removal of "date#([date] , 'YYYY/MM/DD' )": from alt solves the problem.

this is is just one of the format which was present in the earlier version of this file but not now in this file.

In future sone text in the format YYYY/MM/DD can appear again so i will have to use this which i believe will work then.

But the alt function is not concerned if the format is NOT present in the file, it is concerned IF IT IS present then it converts to the desired default/set format else if none of the mentioned format is present then it will return date(0) i.e. 30/12/1899.

Again, why do we have to remove "date#([date] , 'YYYY/MM/DD' )" from the alt to make it work ?

Regards

[kaushiknsolanki]

HI,

     I am not sure why it is behaving like that, but try by putting that statement at the end i.e before num#() function in your expression and see how it goes.

Regards,

Kaushik Solanki

[#9]

did u actually see what i'm using ?

there's nthing wrong in the syntax.

Regards

[#10]

Hi Kaushik,

If you're nt sure why this is happening then i think its quite a problem as i rate you very high,

I can try putting it at the end and it might work but that won't give a logic,why did it work ?

There is something which we're missing or might be a bug.

Regards

[rwunderlich]

The 'YYYY/MM/DD' does match your "I" values.

30/06/12

The date parsing rules allow YYYY to be a 2 digit year as well. So "30" is interpreted as "2030".

I think you can resolve the problem by moving the "YYYY/MM/DD" to the end of the list. Then your I values should get picked up by the "DD/MM/YYYY" format which is earlier in the list.

-Rob

http://robwunderlich.com