Mar 192010
 

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)