2018-05-19

From UNIX timestamps to SQL datetime

Ever struggled to recall what's the beginning of the UNIX epoch? I certainly did, so here's a function FromUnixTime() to conveniently convert UNIX timestamps to datetime values. The usage is straight forward:

2018-05-16

DBAmp Cookbook updates

A few helper functions have been added to the DBAmp Cookbook. Here's why you need them and how you can use them in your code.
  1. fn_GetSFDate() and fn_GetSFShortDate(). These functions would allow to built SOQL statements that can be used with openquery, but manipulate with dates in SQL format. Consider this example:
  2. fn_GetAllReports() – an example of local function. This function returns all the employees reporting – both directly and indirectly – to the provided manager. The function uses common table expressions in MS SQL. In case you are not familiar with this concept, it allows you to define recursive select statements, which is a perfect fit for traversing  organizational hierarchy. As it's executed locally, to use this function you need to replicate User object to the local DB. 
  3. SalesforceId18() is a helper function that safely returns 18 digit Salesforce IDs even if a 15 digit ones provided as an argument. Unfortunately, I couldn't find the original implementation of this transformation, but it ported from a SF formula implementation. On SF side you don't need to invent a wheel anymore, as formula function CASESAFEID(id) will provide conversion.