Solved

How to store and display ONLY time value of datetime field

Posted on 2010-11-30
6
1,059 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 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 50

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

710 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