?
Solved

Extract time from datetime data type column

Posted on 2007-12-04
3
Medium Priority
?
7,265 Views
Last Modified: 2013-11-30
I have a database that has several datetime columns.  The thing is all I care about is the time portion of the column.  The thing is I need to retain the numeric time value as I will need to do time difference calculations on the remaining time values.  I know I cannot store this data in SQL as a datetime but I want to be able to display the time without on my reports without showing the bogus date used by SQL when you only load the time value in a datetime column.  Any guidance would be appreciated.  
0
Comment
Question by:ktQueBIT
[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
3 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20401498
SELECT convert(VARCHAR,GETDATE(),108)
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20401662
If your datetime field is dt, then

select convert(datetime, convert(varchar, dt, 108)), other1, other2 from table1

will result in the time portion only remaining.

To do time different calculations between two datetimes

select dt1 - dt2 from table1

the resulting type is datetime
0
 
LVL 6

Accepted Solution

by:
PaultheBroker earned 1000 total points
ID: 20411329
Although you don't need the date interpretation of the zero (1st Jan 1900), you are obviously best to leave this in datetime format for ease of calculation as Mitch shows above.  As mentioned above, when displaying the date in your reports there are several formats you can use to dispaly only the time using the CONVERT(datatype,date,format) function that are mentioned above.  8 or 108, 14 or 114, or one of the other formats (see BOL for full list) in combination with a RIGHT(string,length) function to chop off the unwanted date.  i.e right(convert(char(19),myTime,100),7) will return 7:23PM.

You might like to wrap whichever you choose into a simple UDF so that it is easy to consistently dispaly the time in your reports (and so you can easily changee which format you use just by changing the UDF definition....)
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

777 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