Solved

Extract time from datetime data type column

Posted on 2007-12-04
3
7,263 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 250 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

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…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 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