cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
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!