Wierd situatuin with MSDE, DateTime problem cannot both select by time and return time only
Posted on 2004-04-21
First of all you should know i just migrated from Access to MSDE.
I used the upsizing wizard to get from Access to SQL, now my problem is the DateTime fields, in Access i had fields which only stored the time ea. "14:00:00", the upsizing wizard has transformed this into a Datetime fields and set it to short Time, this means that it at view time (Using access to view data) looks okay "14:00" but if i click it this value appears 30-12-1899 14:00:00, well i would care if it didnt reflect my availability to run queries and select data but it does, and in a wierd way. When 30-12-1899 is infront of the time i cannot do ALL of my 100 queries which uses the time in a where sentence ea. where lane_time>'13:00'. But and this is the wierd thing when i select the lane_time field and prints it in VB i get rs.Fields("lane_time") = "14:00".
All new value added like this
rs!lane_time = "14:00"
would get 30-12-1899 in front which messes up select but gives okay returns
Okay well this means that i cant do selects on time in SQL, not true, if i every time i add a new record add 01-01-1900 in front of the time, i can actually do the selects with time in where. But the rs.Fields("lane_time") returns = 01-01-1900 14:00:00 which is wierd, because either the value is 14:00 in both selects and returns or its not.
Is it true that SQL server is completely wierd here ? is there no way to get both select and returns working ???