If you use native SQL access to your SalesLogix 6.2 database, then you need to be aware that all dates are stored in coordinated universal time, or UTC (formerly known as Greenwich Mean Time or GMT). What this means is that if you write directly to the database via the native SQL provider, for things such as an initial import or other non-sync aware updates, then you need to make sure to convert the date values to UTC. Similarly, if you read data directly from the SalesLogix database from other systems that use a native SQL connection then you will also need to convert the dates from UTC to local time.
To be clear, this conversion from a UTC date to local DateTime and vice-versa is only necessary when reading & writing directly to the database via a native SQL provider. When you connect using the SalesLogix provider (or from a SalesLogix script) then the conversion is handled automatically by the provider.
To perform the conversion, we can use the built-in SQL getutcdate() function. The getutcdate() function returns the datetime value representing the current UTC time, which is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running. We can use that in our calculation to convert from UTC to a local DateTime and from the local DateTime to a UTC DateTime along with other built-in and easy to use SQL functions such as getdate(), datediff, and dateadd.
To Convert UTC to Local DateTime
/* convert UTC to datetime */ declare @datevalue as datetime set @datevalue = getdate() --set to current local datetime for this example --this will return the value of @datevalue as its UTC datetime value select dateadd(hh, datediff(hh, getdate(), getutcdate()), @datevalue) as Local_DateTime
To Convert Local DateTime to UTC
/* convert datetime to UTC */ declare @utcvalue as datetime set @utcvalue = getutcdate() --set to current UTC datetime for this example --this will return the value of @utcvalue as its local datetime value select dateadd(hh, datediff(hh, getutcdate(), getdate()), @utcvalue) as UTC_DateTime