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
Solved

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

Posted on 2013-05-22
6
402 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 75

Accepted Solution

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

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
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 …

840 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