converting datetimeoffset to datetime

To preserve time zone information, use CONVERT(datetime, @yourDateTimeOffset, 1). This particular usage of 1 in what is otherwise called the style parameter doesn’t seem to be documented for datetimeoffset > datetime, but does work.

declare @createdon datetimeoffset
set @createdon = '2008-12-19 10:00:00 +10:00'

select CONVERT(datetime, @createdon)
--Output: 2008-12-19 10:00:00.000

select CONVERT(datetime, @createdon, 1)
--Output: 2008-12-19 00:00:00.000

trailing spaces

Trailing spaces are a bit whacky in mssql, check this.

debug messages in sql profiler

In profiler enable event UserConfigurable:0, then:

EXEC sp_trace_generateevent 82, N'My message'