Subscribe Bookmark RSS Feed

Formating Column to Date/Time codes

Hegedus

Community Trekker

Joined:

Jun 23, 2011

Scripting an import script.  I have two columns that I create from a text column.  The columns are created properly in that the convert to numbers and I can manually set them to the proper format via the column info dialog.  I am attempting to set them during the import script.  The column "Date" is formatted correctly and column stays a numeric with best.  I can manually set column Date/Time to the correct format.  What is amiss in the formatting line

One caveat: In the column info dialog box if a select a time format and then choose a different time format, I need to first select "best" before the second format is applied.

example:

Date     Date/Time

2012-11-093435311424

new column(

  "Date",

  Numeric,

  Continuous,

  Format("yyyy-mm-dd"),// this works as expected

  formula(Date MDY(

  Num( Substr( Substr( :FileName, Length( :FileName ) - 16, 13 ), 1, 2 ) ),

  Num( Substr( Substr( :FileName, Length( :FileName ) - 16, 13 ), 3, 2 ) ),

  Num( Substr( Substr( :FileName, Length( :FileName ) - 16, 13 ), 5, 2 ) ) + 2000)),

);

new column(

  "Date/Time",

  Numeric,

  Continuous,

  Format("yyyy/mm/dd hh:mm:ss"),//this does not

  formula(:Date + In Hours( Num( Substr( Substr( :FileName, Length( :FileName ) - 16, 13 ), 8, 2 ) ) ) +

  In Minutes( Num( Substr( Substr( :FileName, Length( :FileName ) - 16, 13 ), 10, 2 ) ) ) + Num( Substr( Substr( :FileName, Length( :FileName ) - 16, 13 ), 12, 2 ) )),

);