Table column datetime type but want time only

[schwarzs]

I am trying to define a column so that it is time only. I convert the field to date time after cleaning the field from bad time formats. I want it to be in time format. NOT USEING THE CHAR DATA TYPE BUT DATETIME DATA TYPE!! I can do this if I type a time in the datetime field it stays as time only but, I can't make it stay that way in a sql update query. Is there a way with a User Defined Data Type and/or with a rule.

 

[tlbroadbent]

The datetime data type holds both date and time. Date is the number of days before or after Jan 1, 1900. Date occupies the left 4 bytes of the datetime column. Time is a representation of the number of milliseconds after midnight and occupies the right 4 bytes of the column.

When you enter a time only, the date portion is set to Zero. Zero is Jan 1, 1900. When you enter a date only, the time is set to zero. This represents midnight.

When you use a query to select a datetime column, the default format will show date and time. If the date s zero the column will display as jan 1 1900 12:09:00 or similar. Various tools have different datetime display formats. It is important to learn to differentiate between storage format and display format.

Often, people who worked in Access became accustomed to formating datetime columns in the data sheet view. However, SQL Server doesn't provide a sophisticated GUI interface. You'll have to learn about formating date and time data. The best place to start is with the Cast and Convert topic in SQL BOL.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_2f3o.asp

See also...

http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_9xut.asp

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_03_27c5.asp

http://tlbroadbent.home.attbi.com/sql_and_access/sql_server_datetime.htm

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.