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]

© 2016 BizTalk Blog Suffusion theme by Sayontan Sinha