Solved

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

Posted on 2013-05-22
6
418 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

630 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