Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

invalid date representation

I've created at Holiday table in excel, trying to use to determine work days between two dates.   Looks like everywhere the date column is date format (both in the excel file, and after importing) bu...
  • Anonymous's avatar
    Anonymous
    10 years ago

    Thanks for the help.  Table1 is a calendar table, but I found that the first date being passed was earlier than the first date in the table.  The message indicating "invalid date representation" seemed like there was a problem with the data format of the column in the calendar table.   I further reviewed the table with the dates being passed and found the unexpected dates.   I expanded the calender table and all worked. 

MattAllington's avatar
MattAllington
Community Champion
10 years ago

Is table1 a calendar table?  Datesbetween is designed to be used over calendar tables. You need to pass a start date and an end date, but you are passing columns of dates - you can't do that.  Try wrapping the 2 query columns in MIN for the starting column and MAX for the end column. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    Thanks for the help.  Table1 is a calendar table, but I found that the first date being passed was earlier than the first date in the table.  The message indicating "invalid date representation" seemed like there was a problem with the data format of the column in the calendar table.   I further reviewed the table with the dates being passed and found the unexpected dates.   I expanded the calender table and all worked.