Alt function question

[#1]

Hi All,

I have a question I was hoping someone would be able to help me with. I am trying to write a command to fetch the date format from a column filled with dates: some are ddmmyyyy, some are mmddyyyy and so on and so forth. I was advised to use the ALT function, which now takes the following shape:

ALT( date#([Date], 'DD/MM/YYYY'), date#([Date], 'MM/DD/YYYY'), date#([Date], 'DD/MM/YYYY'), 'No Date Found') AS [Date Type]

But the function populates my 'Date Type' column with 'No Date Found' and doesn't recognise any of the dates in the 'Date' String. I tried running a trial command using ALT(date#([Date], ' correct date format specified in the initial set command'), 'No Date Found'), and the function still returns 'No Date Found'.

Any ideas on how to fix that?

[CELAMBARASAN]

Did you tried Date function around the Date# ?

ALT( Date(date#([Date], 'DD/MM/YYYY')), Date(date#([Date], 'MM/DD/YYYY')), Date(date#([Date], 'DD/MM/YYYY')), 'No Date Found')

[anbu1984]

You said your input is of format ddmmyyyy, mmddyyyy without '/'. But you have used '/' in Alt.

[Colin-Albert]

With your dates being DDMMYYYY or MMDDYYYY how can you tell if 01022014 refers to the 1st February 2014 or 2nd January 2014 ?

[tresesco]

Date#() is not being able to parse it because of wrong format. Try(without '/' symbol), like:

ALT( date#([Date], 'DDMMYYYY'), date#([Date], 'MMDDYYYY'), date#([Date], 'YYYYDDMM'), 'No Date Found') AS [Date Type]

[MK_QSL]

If you have two different formats like

DD/MM/YYYY and MM/DD/YYYY

There is no possibility to recognize them..

12/03/2014 and 12/03/2014.... Both could be either 3rd December or 12th March

ALT function can be used for more than one date formats something like

DD/MM/YYYY and DD.MM.YYYY and DD-MM-YYYY

or

DD/MM/YYYY and MM.DD.YYYY and YYY-MM-DD

Hope this will make clear...

[#7]

I used yyyyddmm and so forth as shorthand, the default format specified uses '/'s.

[#8]

Thanks for your response. I tried 'DDMMYYYY' and so forth, as well as 'DD-MM-YYYY', to no avail.

[#9]

I see- that actually makes perfect sense. I import my data from an excel spreadsheet and I was hoping that maybe it checks out the excel formatting while importing the data strings. Do you think there is any way to check the actual data format (DDMMYYYY vs MMDDYYYY and so forth)?

[Colin-Albert]

With some dates the difference is obvious -  31122014 can only be DDMMYYYY, but for dates in the first 12 days of the month, you cannot tell id a date is DDMMYYYY or MMDDYYYY as both options return valid dates.

02122014 can be 2nd December or 12th February.

You will need to include some other indicator to show what date format is being used unless you can extract all dates in a common format.