Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I'm trying to import a excel table that has a column with dates. My query works without the column date. I'm using ODBC to import.

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ActiveWorkbook.FullName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
cn.Open strCon
cn.Execute "INSERT INTO [ODBC;DRIVER=SQL Server;SERVER=MYSERVEREXPRESS;Trusted_Connection=Yes;DATABASE=TESTDB].[tbTableTest1] ([opDate], [columnFloat], [columnText]) SELECT t.MY_DATE, t.COL1, t.COL2 FROM [BD$] t"

My table:

MY_DATE     COL1    COL2
11/26/2012  21,43   MAR
11/26/2012  21,45   GFM

What am i doing wrong?

share|improve this question
As a quick test, create a table where my_date is defined as an integer and try re-importing. If that solves the issue, I have your answer ;) – billinkc Nov 27 '12 at 18:46
i works as i expected. But what i need to do to enter my date as a date. Use format "dd/mm/aaa" or "mm/dd/aaa" – Makah Nov 27 '12 at 18:49
I always find using Integration services is easier to deal with when importing Excel Files. You may want to give that a try. – Zane Nov 27 '12 at 18:50
But im using SQL Express 2008 and i need to import it programmatically. – Makah Nov 27 '12 at 18:55
1  
Try SELECT CAST(T.MY_DATE AS DATETIME) AS MY_DATE – Zane Nov 27 '12 at 19:24
show 1 more comment

2 Answers

up vote 2 down vote

Internally, Excel stores dates as an integer value. I've run into this before and solved it both in SSIS and straight TSQL

http://stackoverflow.com/questions/9085928/reading-in-date-column-if-first-row-isnt-a-date-with-ssis-excel-data-source/9086831#9086831

In your case, you might need to make a computed column that actually applies the formula, assuming you don't have to worry about the 1900 date.

-- code approximate as I'm late for a meeting
ALTER TABLE tbTableTest1
ADD opActualDate AS DATEADD(day, opDate - 1,CAST('1899-12-31' AS datetime))
share|improve this answer
up vote 0 down vote accepted

Thanks Guys.

I just transformed DATE/Number format to String format.

=TEXT(<DATE_COLUMN>;"mm/dd/yyyy")
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.