Convert epoch data to date

The SQL datetime type does not have a "UTC offset" value.

Your code returns a UTC datetime, which is correctly set to 2018-08-29 06:44:20. If you want to convert that to a different timezone, you're going to need to use a timezone-aware type: the datetimeoffset.

CREATE OR ALTER FUNCTION dbo.fn_EpochToDateTimeOffset (@Epoch bigint)
RETURNS datetimeoffset(0)
As
BEGIN
    DECLARE @Days int, @Milliseconds int, @Utc datetimeoffset(0);
    SET @Days = @Epoch / (1000 * 60 * 60 * 24);
    SET @Milliseconds = @Epoch % (1000 * 60 * 60 * 24);
    SET @Utc = DateAdd(millisecond, @Milliseconds, DateAdd(day, @Days, '19700101'));
    Return @Utc At Time Zone 'India Standard Time';
END;
GO
SELECT dbo.fn_EpochToDateTimeOffset(153552506000);
/* 2018-08-29 12:14:20 +05:30 */

AT TIME ZONE (Transact-SQL) - SQL Server | Microsoft Learn[^]