Inserting Time Only

[a02460]

Hi,

I was wondering if it was possible to input TIME only into a datetime field through the use of the insert statement? If so, how do i do it. Everytime i insert with just time, QL automatically appends the date segment to it. Please help!

 

[SQLBill]

Nope, can't do it. 'SQL Server dates come in two varieties: datetime types and smalldatetime types. There is no separate time data type -- dates and times are always stored together in SQL Server data.' (quoted from The Guru's Guide to Transact SQL by Ken Henderson).

-SQLBill

 

[malexanian]

You can fake it. Just write an update/insert trigger that takes the datetime, strips the date, and appends an arbitrary, constant date, e.g. 1/1/2000. Then all times will be stored as times on this date. Filters and sorts work properly because the date is the same. Just hide the date when displaying the time in your app.

Hope this helps!

 

[SQLBill]

malexanian is correct, but there's an easier way to use a 'default' date. Just enter the time without the date and it will default to January 1, 1900. Then when you do a query, just pull the time portion.

But as I posted before, there's no way to save just the time using DATETIME or SMALLDATETIME datatypes.

-SQLBIll