When Linking to SQL DB, Data Type Time comes across as Small Text
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.
ASKER
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.
This worked. However, it will probably cause issues down the road as far as filters are concerned.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> You will have to use datetime in both cases and set the appropriate format in MS Access.
True. (No points).
/gustav
True. (No points).
/gustav
ASKER
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
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
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