CRM dates how to convert to local time with daylight savings

There are many occasions such as reports that we want to show a date field in CRM and convert it to local time with daylight savings.

Thanks to one of my colleagues add this to your SELECT statement and it will convert a CRM date time field to local time and consider daylight saving:

DATEADD(HOUR,CAST(left(right(CAST(SYSDATETIMEOFFSET()as nvarchar(50)), 5),2) as INT),new_CRMDateTimeField)