Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

When Linking to SQL DB, Data Type Time comes across as Small Text

Posted on 2013-05-22
6
Medium Priority
?
439 Views
Last Modified: 2013-05-23
I just need to store time in this column. What data type do I use in SQL to store time only? And, how do I format the column in Access to allow the user to enter time only? As a side note, I had this same issue with the data type "Date". It would come across in Access as a small text data type. I had to change data type in SQL table to "smalldatetime". Now my date selector works on my Access form and the date is displayed as mm/dd/yyyy. Now I just need to get the time portion working correctly. Access 2013  SQL Server 2012.
0
Comment
Question by:rodneygray
6 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39188621
as you are using SQL Server 2012

You should use DATE data type for your data
and TIME data type for your time

More data on these can found here

DATE : http://msdn.microsoft.com/en-us/library/bb630352.aspx
TIME : http://msdn.microsoft.com/en-us/library/bb677243.aspx
0
 
LVL 1

Author Comment

by:rodneygray
ID: 39188635
Just changed data type to "smalldatetime". A date is appended to the front of the time.1/1/1900. And, if I try to change the value I get a connection error. Also, changed the value to varchar(7) and set Access input mask to 99:00\ >LL;0;_

This worked. However, it will probably cause issues down the road as far as filters are concerned.
0
 
LVL 1

Author Comment

by:rodneygray
ID: 39188642
Neo,
Thanks for the quick reply. And, you are right, DATE and TIME data types are fine as far as SQL is concerned. However, I am using Access as a front end. DATE and TIME data types are converted to "short text" when a connection is made to the SQL database. I am using DSN-Less connection.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 39189596
You will have to use datetime in both cases and set the appropriate format in MS Access.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 39189926
> You will have to use datetime in both cases and set the appropriate format in MS Access.

True. (No points).

/gustav
0
 
LVL 1

Author Closing Comment

by:rodneygray
ID: 39191014
I used smalldatetime and it appears to work fine. Just for others who might read this, the difference between smalldatetime and datetime are as follows:
smalldatetime: 4 bytes,  Jan 1,1900 thru June 6, 2079. Stores time with an accuracy of 1 minute.
datetime: 8 bytes, Jan 1, 1753 thru December 31,9999. Stores time with an accuracy of 3.33 milliseconds
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question