Importing data from excel and inserting into sql tables
excel, sql, sql-server
Solution
Excel stores dates and times as number-values from 0 to 0.99999999 +x days.
0.29166666667 would be like `00.01.1900 07:00:00`, which seems to be correct in your case.
So, you would have to use some reformatting or conversion of this value, before using it as a direct string-input.
In VBA you could use `Format(myValue,"DD-MM-YYYY hh:mm:ss")`.
The equivalent worksheet function would be `TEXT(A1,"DD-MM-YYYY hh:mm:ss")`.
The format-code depends on your regional settings. You might want to try something like this `Format(myTime, "Long Time")`, if you preffer to use excel-defined time-formats.
Because you did not post any code, I am not sure about how you import your excel-data. But I would say, the fastest way to get better results, would be setting up a new column, using the `TEXT` formula with a reference to the previously time-column and use this new formatted column as input for your sql-db.
Problem
I am Importing data from excel and inserting into a sql table. One of the fields in my Excel file is populated with a time value. I want to import that time value as a string into my sql table. When i do that i get a weird value in my sql table. Excel value is: 07:00 and after inserting that as a string into the sql table the time values looks like this: 0,29166666667. The reason for importing it as a string value is that you have to be able to define Days in the same field. Like this : D2 10:30. When i import this kind of values it is inserted correctly. can anyone help ?