MSSQL 2005 - time only field - Databases forum at WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

MSSQL 2005 - time only field

gbdg

4:07 am on May 6, 2008 (gmt 0)

Folks, I'm trying to define columns in a MSSQL 2005 table that contain a time value, and that alone.

I imported time data only (using import from a .csv file) and ended up with values such as "12/30/1899 11:00:00 AM"

This article looked interesting but when I applied these changes to a copy of the target table, my import would not work:
[weblogs.sqlteam.com...]

Next I tried applying the proposed changes to the table into which I had originally imported the data, but nothing changed - the dates still appear alongside the times, and I am not allowed to delete the dates.

Could anyone point me into the right direction to store ONLY time data in a column please?

Thanks much.

LifeinAsia

3:58 pm on May 6, 2008 (gmt 0)

As a DATETIME field, it will have a date component and a time component. You could change it to VARCHAR, but then you'll lose a lot of the dattime functionality without constantly converting back and forth. About the only other option is to upgrade to MSSQL 2008, which does have a true TIME data type.

syber

6:00 pm on May 6, 2008 (gmt 0)

You could accomplish what you want by creating a virtual (formula) field for the table that would display only the time portion of an existing datetime field:

ALTER TABLE mytable
ADD timeonly AS (str(datepart(hour,[datetimefield]),2) + ':' + str(datepart(minute,[datetimefield]),2) + ':' + str(datepart(second,[datetimefield]),2))

You could then query:

SELECT datetimefield, timeonly FROM mytable