How to store and display ONLY time value of datetime field

I can connect to sql server from Microsoft Access. If I put a time value in a datetime field, it displays in the table correctly. eg.  4:00:00 PM
If I try to input a record using sql stored procedures, it always stores and displays the date 1/1/1900 in front of the time even though I send the time to the table like this: CONVERT(char(10), Login, 8).  How can I write a stored procedure that will store and display ONLY the Time value. It's obvious that the table can store it that way because it works when sending it from Access.
LVL 1
BradleyClevelandAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Gustav BrockConnect With a Mentor CIOCommented:
1. Both SQL Server and Access always store both date and time in a field of data type Date.

2. The base date for "time only" is 1900-01-01 for SQL Server while it is 1899-12-30 for Access.

3. When a date/time value has the date of the base date, only the time part is displayed.

4. Any date/time value can be displayed as time only by proper formatting.

Thus, you have these options:

a. Ignore the issue and format your values to display the time part only.

b. Record the time values in SQL Server with the base date of Access, for example: "1899-12-30 00:04:00". This will read in Access as "time only".

c. Record the time values in SQL Server with the base date of SQL Server, for example: "00:04:00". When you wish to read the values in Access, convert the date/time values to have the base date of Access:

datTime = DateAdd("d", -2, datTimeFromSQLServer)

In Access, this may work as well in a query:

TimePart: Int([YourTimeField])

/gustav
0
 
joeviCommented:
select convert(varchar(10),login,108)
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The database always stores the Date + Time. If you want to store ONLY the Time, you'll have to do that in a separate field set to Double or something of that nature.

Viewing ONLY the time is UI consideration; in other words, you'd typically format the data the way you wish to view it when you fill your form.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
GSGDBACommented:
Hi
What SQL server version are you using?
0
 
CboudrozCommented:
Time field don't exists in SQL 2000 and SQL 2005.  In SQL 2008 you can have a variable or column of type "Time".

The MS SQL base date is :1900-01-01

So if you store time in a datetime column you will see the time on the base date.

You can  used integer to store minute or hours or Second --> sometimes it's good (like execution duration)
You can also used string to store the "05:00" --> worst way

, but I will prefers working whit datetime and have all my time record on 1900-01-01, Except if I can find a way to used the real date.

   
0
 
Scott PletcherSenior DBACommented:
You can't change the way it stores, except by changing to a non datetime format.  

You can indirectly change the way it displays.

Create a separate, computed column that displays the time exactly as you want it, then use that column in your display queries.

ALTER TABLE table1
ADD time AS CAST(REPLACE(RIGHT(CONVERT(varchar(30), datecolumn, 0), 7), ' ', '0') AS char(7))

If you don't want a leading zero in times before 10AM/10PM, change the REPLACE to an LTRIM.

If you want AM/PM in lower case, add a LOWER() around the expression.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.