Just show time in datetime column sql server 2005

martgriff
martgriff used Ask the Experts™
on
I have a datetime column in a table in sql server 2005, can i have just the time being displayed as currently it adds the date also?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
I assume that you mean when you query the column.
This link talks about the various methods to strip out time and date:
http://databases.aspfaq.com/general/why-doesn-t-sql-server-allow-me-to-separate-date-and-time.html
Commented:
select convert(char(8), datetime_column, 8)
returns 00:00:00

Select right(char(20),convert(char(8), datetime_column, 0),7)
returns 0:00am

While the url provides essentially the same info, I found it misleading.  SQL does not insert dates to times or times to dates only.  Datetime is a float value.  when date is missing the value is less than 1.  When time is missing the value is an integer.  Nothing is "added".

Convert a datetime to date only use

cast(datediff(day,0,datetimecolumn) as datetime) which has the same effect as making an integer of the float storage value.

Tom

Author

Commented:
No i meant just storing the data in the column, but dont think you can store just the time.

Will have to pull either date or time out in the query instead.

Thanks

Commented:
You can store the time portion only in a column by using one of the following formulas on a datetime value.
If you are storing a float value,
time_fl = cast(datetime_col as float) - cast(datetime_col as integer)

If you are storing a datetime value

time_dt = datetime_col - cast(datetime_col as integer)


select time_fl, time_dt

will return

.3450085185,   1900-01-01 08:16:55.360

You can use convert() to display the time appropriately.

Tom

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial