Converting UTC to local time

Problem

Values in the database are stored as UTC date and time in a simple datetime format. You want to see data time values in the local time.

Solution

Use the following code to convert UTC date/time to local:

CREATE TABLE #table (date_UTC datetime)

/* Populate with UTC date time */

Insert #table Select GETUTCDATE()

Select

/* UTC value */

date_UTC

/* convert UTC to local time */

,DATEADD(MILLISECOND,DATEDIFF(MILLISECOND,getutcdate(),GETDATE()),date_UTC) as date_In_LocalTime

From

table

Use sp_replrestart to fix “The process could not execute ‘sp_repldone/sp_replcounters’” error

This error is generally raised by the log reader agent while running transactional replications. The error indicates that distributor and subscribe databases has data that may be more recent than data in the publisher database (the agent compares latest LSN in transaction logs of Publisher database and latest LSN recorded in the distributer).

This problem usually occurs after publisher database has been restored from a backup. 

To resolve the issue, run sp_replrestart stored procedure in the publisher database and then reinitialize the subscribers. Also, consider running DBCC CHECKDB on publisher database to validate consistency.