Sample 1:
declare @createTime datetime = '2012-10-06 02:29:37.243'; select @createtime as originaltime, dateadd(day, -4, dateadd(hour,-1,@createtime))as minus4daysandhour
Sample with a table:
select DWDOCID, DWSTOREDATETIME, Getdate() as RightNowDateTime, DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),DWSTOREDATETIME) AS currentLocalTime from [dbo].[PE_DOCUMENTS] where DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),DWSTOREDATETIME) < DATEADD(MINUTE, -30, GETDATE())
Results (New York Time):