Solved

How to store and display ONLY time value of datetime field

Posted on 2010-11-30
6
1,032 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
6 Comments
 
LVL 4

Expert Comment

by:joevi
ID: 34241366
select convert(varchar(10),login,108)
0
 
LVL 84
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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:ScottPletcher
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 49

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

896 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now