As everyone already knows, to extract the time from a datetime (pre SQL 2008), you have to use the convert function
HOWEVER: beware of using the smalldatetime type, as it trucates the seconds from the value convert returns
Run the following code:
declare @thissmalldatetime smalldatetime,@thisdatetime datetime select @thissmalldatetime=getdate(),@thisdatetime=getdate() select convert(nvarchar(8),@thissmalldatetime,14) as [Small Date Time],convert(nvarchar(8),@thisdatetime,14) as [Date Time] waitfor delay '00:00:01' select @thissmalldatetime=getdate(),@thisdatetime=getdate() select convert(nvarchar(8),@thissmalldatetime,14) as [Small Date Time],convert(nvarchar(8),@thisdatetime,14) as [Date Time]
Small Date Time Date Time --------------- --------- 13:13:00 13:12:40 (1 row(s) affected) Small Date Time Date Time --------------- --------- 13:13:00 13:12:41 (1 row(s) affected)