DateTime saved as numeric value using SqlBulkCopy

Question

I'm using SqlBulkCopy to save a read-only Excel sheet into a predefined SQL table.
There are several date columns in the sheet, but not all the rows have date values(empty and '#').
The corresponding columns types are varchar(20) and not smalldatetime, because otherwise I get an error that the data can not be converted.

After running SqlBulkCopy the dates are saved as numeric values in a pattern that I can't figure out.
For example:
31.12.2010 is saved as 40543
31.13.2011 is saved as 40908
30.6.2011 is saved as 40724

Now I have to present the date in web forms. The numeric values don't bother me as long as I can convert them back to date format for presenting in a GridView, but I don't know how.

Would appreciate any help with this.

score 1 · Answer 1 · answered Jun 23 '13 at 14:18

1

Try running this query:

SELECT CAST(CAST(YourVarcharDateColumn AS int) AS DateTime) FROM YourTable

answered Jun 23 '13 at 14:18

Ben Narube

448
3
10

this query returns the wrong value of the date. it always deviates a few days. – Yevgeni Grinberg Jun 25 '13 at 08:21

score 0 · Accepted Answer · answered Jun 23 '13 at 14:34

0

I used the following:

DateTime.FromOADate(num).ToShortDateString();

where num is the numeric value of the date

answered Jun 23 '13 at 14:34

Yevgeni Grinberg

359
5
19

DateTime saved as numeric value using SqlBulkCopy

2 Answers2