Excel (xlsx) DB and date/time

Need help? Post a question or browse the topics for technical support in using Noventri products, peripherals, and accessories.
Post Reply
icevault
Valued Contributor
Valued Contributor
Posts: 28
Joined: Wed Apr 17, 2013 11:16 am

Excel (xlsx) DB and date/time

Post by icevault »

I have attached an Excel file (xlsx) as a database and I'm trying to set up the SELECT statement to pull the appropriate data.

The Excel has one column for DATE and a separate one for TIME. The formats are mm/dd/yyyy and hh:mm:ss am/pm.

When I hit refresh to display the result, it shows the DATE as mm/dd/yyyy hh:mm:ss am/pm and the TIME column sows the following:
Error: date out of range!

Everything looks fine on the Excel file. Any ideas why this error appears?
Martin-Noventri

Re: Excel (xlsx) DB and date/time

Post by Martin-Noventri »

Hi icevault,

It could be that the Excel column is not set to the applicable format. The "general" format is the default for Excel, so make sure the columns are set to the appropriate format for your data type .

Another option is to cast it with the appropriate function in the select statement, such as Cstr, CDate etc. in Noventri Suite.

Kind regards,
Martin
Post Reply