cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Try the Materials Informatics Toolkit, which is designed to easily handle SMILES data. This and other helpful add-ins are available in the JMP® Marketplace
Choose Language Hide Translation Bar
msharp
Super User (Alumni)

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
msharp
Super User (Alumni)

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

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)
       );
);

 

View solution in original post

3 REPLIES 3
txnelson
Super User

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

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.

Jim
msharp
Super User (Alumni)

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

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)
       );
);

 

Arthur_Wesley
Level II

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

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