Solved

How to store and display ONLY time value of datetime field

Posted on 2010-11-30
6
1,064 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:BradleyCleveland
[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 4

Expert Comment

by:joevi
ID: 34241366
select convert(varchar(10),login,108)
0
 
LVL 85
ID: 34241423
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
 
LVL 3

Expert Comment

by:GSGDBA
ID: 34241460
Hi
What SQL server version are you using?
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 7

Expert Comment

by:Cboudroz
ID: 34241483
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 34242145
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
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 34245869
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

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

635 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