Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to store and display ONLY time value of datetime field

Posted on 2010-11-30
6
Medium Priority
?
1,071 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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 70

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 2000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

688 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