DateTime field conversion from Access 2003 to SQL 2000

[MikeSawt]

I am new to SQL and I am trying to convert an Access 2003 database to Microsoft SQL Server 2000 backend.

I am using combo box in Access that is linked to a SQL table/query with the time information.
I have the properties in Access set to “Medium Time” and this displays as 8:30 AM once the data is in the field.


However, with the use of the dropdown and query the user sees“1900-01-01 08:30:00”

What is the correct Data Type in SQL to be able to view the time data as “8:30AM” instead of viewing it “1900-01-01 08:30:00”?


As a workaround, I have written a query on my font end that coverts the dropdown “1900-01-01 08:30:00" to a text fields and I hide the bound column, so the user only sees the text “8:00 AM” , but when the user goes back to adjust the time the “1900-01-01 08:30:00” reappears during the adjustment but goes back the 8:30 AM as controlled by the properties in Access.

This data needs to be in a datetime format in Access because I am using DateDiff to calculate elapsed time.

I hope that there is enough info here to get an answer

MikeSawt

 

[bborissov]

No such type in SQL Server. It has only variations of DateTime type and you must handle this in your frontend (as you do already). You could convert that field to var/char when you query it and when you need to pass it back (Update/Insert) you must convert it back to datetime one.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLSister]

If you just want the query to display the time, you can use convert with style 8. Look up how to do this in BOL.

"NOTHING is more important in a database than integrity." ESquared

 

[mrdenny]

Once SQL Server 2008 is released Q1 or Q2 of next year you will have access to the TIME data type. Until then converting it as SQLSister and bborissov have already showed you how to do.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com