topic Re: Is there an easy way to know if a column is Date/Time? in Discussions

Is there an easy way to know if a column is Date/Time?

msharp — Thu, 15 Aug 2019 13:21:56 GMT

So I had a script that ran something like this:

for(i=1, i<=ncols(dt), i++,
       name = Column(dt, i) << Get Name;
       if(contains(name, "Date") | contains(name, "Time"),
              //Run code
       )
);

However, I've found that not all columns have "Date" or "Time" in the name. Sometimes due to misspelling (like Tme, Tim, ect.) Sometimes b/c it's just not included b/c the columns are arbitrarily named-"Column 1", "Value 2", ect.
Anyways, I've been experimenting with '<< Get Format', but this doesn't seem to be consistent either.

Most of these files are coming from .csv generated by other programs. Maybe there's a preference somewhere that would default Date formats to be a specific one of interest I could query?

Suggestions?

Re: Is there an easy way to know if a column is Date/Time?

txnelson — Tue, 15 Mar 2016 21:03:38 GMT

I am not aware of any "easy" way to determine that a column is a date value. Your approach to use the << get format, should provide the largest number of correct evaluations.

Re: Is there an easy way to know if a column is Date/Time?

msharp — Thu, 15 Aug 2019 13:20:20 GMT

Thanks for confirming what I feared. I quickly wrote up this code if anyone has similar issues. I am also open to other bright ideas. Thanks!

isDate = Function({col}, {Default Local},
       //Insert Column (col) and returns 1 if column is a date, 0 if false.
       format = char(col << Get Format);
       test = regex(format, "m/d/y|m/y|yyyy|y/m/d|Date|day|h:m|hr:m|min:s");
       if(!ismissing(test),
              return(1),
              return(0)
       );
);

Re: Is there an easy way to know if a column is Date/Time?

Arthur_Wesley — Thu, 15 Aug 2019 12:41:55 GMT

thank you for taking the time to post this code, it works great!