Solved

Extract time from datetime data type column

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Error - Query 6 26
SQL - Copy data from one database to another 6 20
sqlserver get datetime field and create a string 5 18
SQL server vNext 18 31
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

832 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