July 18, 2008 at 11:59 am
This is my firstever post to any forum. I am new in VB as well in Sql
I am trying to import a time column from ExcelShpreadsheet into a SQL table Column that is set to varchar(10) in VB.net . But it gives me a double value instead of time. The time format in the excel spreadsheet id XX:XX:XX AM/PM
Any help will be appreciated.
:w00t:
July 18, 2008 at 12:15 pm
provide more info.
how are u trying to do it etc..
July 18, 2008 at 12:22 pm
I am creating VB.net application
going through each rowand picking the value of a particular column that has Time Format e.g. 03:15:00 AM.
But when I get this in VB.NET variable it gives me a double so I can not insert into table in sql. I get the value .1355555555555. Do not know from where it comes.
The line of the code is
Time = oSheet.Range("F" & j).Value
where time is date object in VB.NET
:w00t:
July 18, 2008 at 2:04 pm
Excel, like SQL, stores dates and times as integers (dates) and decimals (time).
For example, right now, on my computer, getdate() = 39645.66645625
That's the number of days and the number of 300ths of a second since midnight of 1 Jan 1900 (the midnight at the beginning of the day, not the one at the end).
If you cast the decimal number as datetime, it will give you the time on 1 Jan 1900. If you add the decimal to a date, it will give you the time on that date.
Try this, it may make it more clear:
select cast(getdate() as float)
select cast(0 as datetime)
select cast('18 July 2008' as datetime) + .6682513503
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 20, 2008 at 12:22 pm
Thanks,
I did not know that. that is really amazing to me.:)
:w00t:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply