[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7271
  • Last Modified:

Extract time from datetime data type column

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
ktQueBIT
Asked:
ktQueBIT
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT convert(VARCHAR,GETDATE(),108)
0
 
imitchieCommented:
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
 
PaultheBrokerCommented:
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now