Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-05-22
6
Medium Priority
?
432 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 51

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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

670 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