UTC Dates and SalesLogix 6.2

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

ABOUT THE AUTHOR

Ryan Farley

Ryan Farley is the Director of Development for Customer FX and creator of slxdeveloper.com. He's been blogging regularly about SalesLogix, now Infor CRM, since 2001 and believes in sharing with the community. His new passion for CRM is Creatio, formerly bpm'online. He loves C#, Javascript, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

1 Comment

  1. I have a problem and I am not sure this is related. When am Italian PC connects to me SLX terminal server and tries to use SLX, they get a time zone information not found error message. If I go into the timezone gui the correct one is set. If I just click OK, SLX refreshes itself and everything is OK. I dont get this – other countries on the same time zone are OK. If I look at the registry the HKLMSOFTWAREMicrosoftWindows NTCurrentVersionsTime_Zones) does not contain the appropriate time zone as a display_uninstall and there is no timezone_no for W Europe either. Not sure if this is relevant though. Any ideas ?

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!