Скажем, как корректно отобрать данные за период локального времени
с {ts '2017-01-01 05:00:00.000'} по {ts '2017-01-01 06:00:00.000'}?

входящие параметры
@date_from = {ts '2017-01-01 05:00:00.000'}
@date_to = {ts '2017-01-01 06:00:00.000'}

select createdDate  from table
 where createdDate >= @date_from
   and createdDate <= @date_to

alter FUNCTION dbo.local_to_utc_datetime
(
	@p_datetime datetime
)
RETURNS datetime
AS
BEGIN

    return DATEADD(ms, DATEDIFF(ms, GETDATE(), GETUTCDATE()), @p_datetime) 

END


create FUNCTION dbo.utc_to_local_datetime
(
	@p_datetime datetime
)
RETURNS datetime
AS
BEGIN

    return DATEADD(ms, DATEDIFF(ms, GETUTCDATE(), GETDATE()), @p_datetime) 

END

select GETUTCDATE(), GETDATE(), SYSDATETIMEOFFSET(),
SUBSTRING(CONVERT(VARCHAR(100),SYSDATETIMEOFFSET(),121),29,6),
[timeoffset]=cast(SUBSTRING(CONVERT(VARCHAR(100),SYSDATETIMEOFFSET(),121),29,3) as int)

select GETUTCDATE(), GETDATE()

alter FUNCTION dbo.local_to_utc_datetime
(
	@p_datetime datetime
)
RETURNS datetime
AS
BEGIN

    return DATEADD(ms, DATEDIFF(ms, GETDATE(), GETUTCDATE()), @p_datetime) 

END