Issue with ANYDTDTM

[Hari2]

Hi All,

- I have Imported data from Excel file in which I have a column with both date and time Ex: 05/09/2018 9:46. (dd/mm/yyyy hh:mm)

 - I am using ANYDTDTM. to convert it to datetime format and the code I am using is Below.

   input(coll_date_time,ANYDTDTM.) as erdttime format=datetime15.

- Here, we have dates in the form of dd/mm/yyyy hh:mm and expectation from above piece of code is 05SEP18:09:46. However, above code is giving output as '09MAY2018:09:46'. It's considering day as month and Month as Day.

while the same code is giving correct Output for ''29/01/2019 10:40" as 29JAN19:10:40 (here its considering correctly when day is above 12) .

Please provide what should I specify here to get correct output as required. Thanks for your support in advance.

[Kurt_Bremser · Accepted solution]

You need to properly set your DATESTYLE= system option. Or you input date and time separately (use the SCAN() function to get the parts) and combine them with DHMS().

[Hari2]

Thank you Sir.. I used DATESTYLE Option.. 🙂

[Kurt_Bremser]

Personally, I prefer to not use the "any" informats, as they might read something without ERROR that is in fact not correct. I try to force the correct informat as much as I can, so that I get alerted to data mistakes by a crashing import job (because I am responsible for ~1000 batch jobs, several hundred of which are pure import jobs, and I cannot check that manually).

Some of those imports read data that was handled manually on the way, and must be considered unreliable and therefore checked for correctness as much as possible.