Calendar data with 25 Date columns on TS 4511

Team,

     All the 25 columns are defined as "DATE" datatype inside TS database.

   I am using a JSON file below:

21 Date columns have a similar format.

Last two columns have the seconds in Fractions which is resolved.

There two Date columns of this format:

"23 Dec 13"

"23 Dec 2013"

===============================

{
  "database": "my_dwh",
  "schema": "falcon_default_schema",
  "table": "Calendar_Dim",
  "columns":
 [
  {
    "name": "Full_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "Week_Of_Year_Begin_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "Week_Of_Year_End_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "Week_Of_Month_Begin_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "Week_Of_Month_End_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "Week_Of_Quarter_Begin_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "Week_Of_Quarter_End_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "Month_Begin_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "Month_End_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "Quarter_Begin_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "Quarter_End_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "Year_Begin_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "Year_End_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "DDMONYY",
    "date_format": "%d-%Om-%y"
  },
  {
    "name": "DDMONYYYY",
    "date_format": "%d-%Om-%Y"
  },
  {
    "name": "Fiscal_Week_Begin_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "Fiscal_Week_End_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "Fiscal_Month_Begin_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "Fiscal_Month_End_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "Fiscal_Quarter_Begin_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "Fiscal_Quarter_End_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "Fiscal_Year_Begin_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "Fiscal_Year_End_Date",
    "date_format": "%Y-%m-%d %I:%M:%S %p"
  },
  {
    "name": "Row_Effective_Date",
    "date_format": "%Y-%m-%d %H:%M:%S"
  },
  {
    "name": "Row_End_Date",
    "date_format": "%Y-%m-%d %H:%M:%S"
  }
 ]
}

=======================

sample data:

"20131223","Normal","2013-12-23 00:00:00","2","23","84","357","MONDAY","MON","M ","Early","Weekday","2013357","2013041630","Y","N","N","N","N","N","N","N","","","","","2013-12-22 00:00:00","20131222","2013-12-28 00:00:00","20131228","2013-12-22 00:00:00","20131222","2013-12-28 00:00:00","20131228","2013-12-17 00:00:00","20131217","2013-12-23 00:00:00","20131223","4","12","52","12","DECEMBER","Dec","2013-12-01 00:00:00","20131201","2013-12-31 00:00:00","20131231","4","2013-10-01 00:00:00","","2013-12-31 00:00:00","","2013","2013-01-01 00:00:00","20130101","2013-12-31 00:00:00","20131231","201312","20131223","23 Dec 13","23 Dec 2013","Qtr 4, 2013","Dec 2013","26","20131223","2013-12-23 00:00:00","20131229","2013-12-29 00:00:00","6","2013-12-01 00:00:00","20131201","2013-12-31 00:00:00","20131231","2","2013-10-01 00:00:00","20131001","2013-12-31 00:00:00","20131231","2014","2013-07-01 00:00:00","20130701","2014-06-30 00:00:00","20140630","176","84","23","1","Qtr 2, 2014","41630","Other","10","Other","Other","Other","Other","Other","Other","Other","Other","1","0","0001-01-01 00:00:00.0000000","9999-12-31 23:59:59.9970000","True","True","0E0C6792DE7A5E71695E5DECCD5FCE8221A91518",""
=======================

Error at First Date column - Full_Date:

line no=2 ;Reason: Conversion failed, error=Can not convert "2013-12-23 00:00:00" to date using date format "%Y-%m-%d %I:%M:%S %p", data column=3, schema column=Full_Date

=========================================

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • %p indicates that at the end of the date there should be an AM/PM indicator. The lack of that also means you should be using %H not %I.. so more like "%Y-%m-%d %H:%M:%S".

     

    You really need to check each field and it's format as you appear to have a fun variety of formats (although I'm not sure which you're loading as text and which as dates). In general when it flags a problem just convert the value to the format: here

    "2013-12-23 00:00:00"

    "%Y-%m-%d %I:%M:%S %p"

    It should be clear that the %p on the end has nothing to match. 

    Reply Like
  • Great Stuart ! Yes that fixed the issue. Actually when I overlaid the data onto Xcel it displays AM/PM indicator. But not on a raw data file on Linux when you scan it. Good Catch ! Thank you again for your quick response and an immediate resolution ! Awesome !

     

    --

    Lokesh

    Reply Like
Like Follow
  • 6 mths agoLast active
  • 2Replies
  • 276Views
  • 2 Following