time format from access to SQL2k

[fsxtech]

i am having this problem that i hope some of you can help me with......

the "time" in my access database displays as "17:35" and when i import the database into SQL2k it displays it as "1899-12-30 17:35 00:00"

why is this happening??

so when i imported to the access database into SQL2k it asigned this prefix "1899-12-30" to all the time fields

so...if i had a field displaying the time as "20:30" in access

i would display it as "1899-12-30 20:30"

sheesh.....

any help would be greatly admired

thank you all for your help in advance

 

[tlbroadbent]

As in Access, SQL Server dates and times are stored as numbers. You have some control over the storage but not much. You can control how dates and times are displayed.

When you query the time field in SQL Server, you'll need to format it. Access does this very nicely, almost behind the scenes.

Here is a sample query.

Select convert(char(8), TimeCol, 8) From tbl

The third argument for the convert function is style. In this case, I've chosen style 8 which is hh:mm:ss. You can find the other styles listed in SQL BOL. You can view the table of styles under the topic "Cast and Convert". The topic is available online at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

BTW: I don't know why the date is "1899-12-30". It appears that -2 was stored in the date portion of the datetime column for some reason. It won't make much difference but you can set the date portion to zero with an update query like this.

Update tbl
Set TimeCol=convert(char(8),TimeCol,8)

After running this query if you run a select query without the convert function, you'll get 1900-01-01 dates. As I said, it won't make much difference. You'll still need to convert the columns to the display style of your choosing. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin