Assuming SQL 2008 or higher, you'll need to use the
new datetime2(7) type[
^].
SELECT DateAdd(millisecond, -1, Cast(DateAdd(day, -1, Cast(SysDateTime() As date)) As datetime2(7)));
Breaking that down:
DECLARE @Now datetime2(7) = SysDateTime();
DECLARE @Today date = Cast(@Now As date);
DECLARE @Yesterday date = DateAdd(day, -1, @Today);
DECLARE @YesterdayDateTime datetime2(7) = Cast(@Yesterday As datetime2(7));
DECLARE @Result datetime2(7) = DateAdd(millisecond, -1, @YesterdayDateTime);