MS SQL Time Zone Function–Current Version of the code

A while back I published and article on a time zone function for MS SQL.  A few revisions have occurred since then.  The current version of the code is listed below.

table:

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[TIME_ZONES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE [dbo].[TIME_ZONES]

GO

-- Create the TIME_ZONES table

CREATE TABLE [dbo].[TIME_ZONES] (

    [TIMEZONE_CD] [varchar] (6) NOT NULL ,

    [TIMEZONE_NAME] [varchar] (60) NOT NULL ,

    [OFFSET_HR] [int] NOT NULL ,

    [OFFSET_MI] [int] NOT NULL ,

    [DST_OFFSET_HR] [int] NOT NULL ,

    [DST_OFFSET_MI] [int] NOT NULL ,

    [DST_EFF_DT] [varchar] (10) NOT NULL ,

    [DST_END_DT] [varchar] (10) NOT NULL ,

    [EFF_DT] DATETIME NOT NULL,

    [END_DT] DATETIME NOT NULL

) ON [PRIMARY]

GO

-- Alter the table to add the primary key

ALTER TABLE [dbo].[TIME_ZONES] WITH NOCHECK ADD 

    CONSTRAINT [PK_TIME_ZONES] PRIMARY KEY  CLUSTERED 

    (

        [TIMEZONE_CD],

        [EFF_DT]

    )  ON [PRIMARY] 

GO

-- Add the field default contraints

ALTER TABLE [dbo].[TIME_ZONES] ADD 

    CONSTRAINT [DF_TIME_ZONES_OFFSET] DEFAULT ((-1)) FOR [OFFSET_HR],

    CONSTRAINT [DF_TIME_ZONES_OFFSET_MI] DEFAULT (0) FOR [OFFSET_MI],

    CONSTRAINT [DF_TIME_ZONES_DST_OFFSET] DEFAULT ((-1)) FOR [DST_OFFSET_HR],

    CONSTRAINT [DF_TIME_ZONES_DST_OFFSET_MI] DEFAULT (0) FOR [DST_OFFSET_MI],

    CONSTRAINT [DF_TIME_ZONES_DST_EFF_DT] DEFAULT ('03210200') FOR [DST_EFF_DT],

    CONSTRAINT [DF_TIME_ZONES_DST_END_DT] DEFAULT ('11110100') FOR [DST_END_DT],

    CONSTRAINT DF_TIME_ZONES_EFF_DT DEFAULT GETDATE() FOR [EFF_DT],

    CONSTRAINT DF_TIME_ZONES_END_DT DEFAULT '12/31/9999' FOR [END_DT]

GO

-- Insert time zone information into the TIME_ZONES table

INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)

VALUES('AKT','Alaskan Time',-9,0,-8,0,'03210200','11110100')

GO

INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)

VALUES('AT','Atlantic Time',-4,0,-3,0,'03210200','11110100')

GO

INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)

VALUES('AZ','Arizona Time',-7,0,-7,0,'03210200','11110100')

GO

INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)

VALUES('CT','Central Time',-6,0,-5,0,'03210200','11110100')

GO

INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)

VALUES('ET','Eastern Time',-5,0,-4,0,'03210200','11110100')

GO

INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)

VALUES('HT','Hawaii Time',-10,0,-10,0,'03210200','11110100')

GO

INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)

VALUES('MT','Mountain Time',-7,0,-6,0,'03210200','11110100')

GO

INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)

VALUES('NT','Newfoundland Time',-4,30,-2,30,'03210200','11110100')

GO

INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)

VALUES('PT','Pacific Time',-8,0,-7,0,'03210200','11110100')

GO

INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)

VALUES('YT','Yukon Time',-8,0,-8,0,'03210200','11110100')

GO

functions:

CREATE FUNCTION [dbo].[UTCTIME]

    (@DT AS DATETIME, 

     @TZ AS VARCHAR(12))

RETURNS DATETIME

AS

BEGIN

    -- DECLARE VARIABLES

    DECLARE @NEWDT AS DATETIME

    DECLARE @OFFSETHR AS INT

    DECLARE @OFFSETMI AS INT

    DECLARE @DSTOFFSETHR AS INT

    DECLARE @DSTOFFSETMI AS INT

    --DECLARE @DSTDT AS VARCHAR(10)

    DECLARE @DSTEFFDT AS VARCHAR(10)

    DECLARE @DSTENDDT AS VARCHAR(10)

    DECLARE @DSTSTARTDT AS DATETIME

    DECLARE @DSTSTOPDT AS DATETIME    

    -- This query gets the timezone information from the TIME_ZONES table for the provided timezone

    SELECT

        @OFFSETHR=offset_hr,

        @OFFSETMI=offset_mi,

        @DSTOFFSETHR=dst_offset_hr,

        @DSTOFFSETMI=dst_offset_mi,

        @DSTEFFDT=dst_eff_dt,

        @DSTENDDT=dst_END_dt

    FROM time_zones

    WHERE timezone_cd = @TZ AND

        @DT BETWEEN eff_dt AND end_dt

    -- Increase the datetime by the hours and minutes assigned to the timezone

    SET @NEWDT = DATEADD(hh,@OFFSETHR*-1,@DT)

    SET @NEWDT = DATEADD(mi,@OFFSETMI*-1,@NEWDT)

    -- Declare variables needed to convert MMWDHHmm pattern to for DST start date

    DECLARE @DSTSTARTMONTH INT

    DECLARE @DSTSTARTINSTANCE INT

    DECLARE @DSTSTARTDAY INT

    DECLARE @DSTSTARTHOUR INT

    DECLARE @DSTSTARTMINUTE INT

    DECLARE @DSTSTARTMODIFIER INT

    -- Perform calculations to determine date

    SET @DSTSTARTMONTH = CONVERT(INT,SUBSTRING(@DSTEFFDT,1,2))-1

    SET @DSTSTARTINSTANCE = CONVERT(INT,SUBSTRING(@DSTEFFDT,3,1))

    SET @DSTSTARTDAY = CONVERT(INT,SUBSTRING(@DSTEFFDT,4,1))

    SET @DSTSTARTHOUR = CONVERT(INT,SUBSTRING(@DSTEFFDT,5,2))

    SET @DSTSTARTMINUTE = CONVERT(INT,SUBSTRING(@DSTEFFDT,7,2))

    -- Set the start date variable to the first day of the year for the year of the date being evaluated

    SET @DSTSTARTDT = CONVERT(DATETIME,'1/1/' + CONVERT(VARCHAR(4),DATEPART(year,@NEWDT)))

    -- Add month value for start pattern to the start date variable

    SET @DSTSTARTDT = DATEADD(month,@DSTSTARTMONTH,@DSTSTARTDT)

    -- Determine the modifier value needed to adjust the date when the date desired is in a 

    -- month that does not start on a Sunday

    SET @DSTSTARTMODIFIER = DATEPART(weekday,@DSTSTARTDT)

    -- Check to see if the modifier date is greater than the date being evaluated

-- This is important because it will change the final modifier

-- (adding days in some instances and subtracting days in others

    IF @DSTSTARTMODIFIER > @DSTSTARTDAY

    BEGIN

        SET @DSTSTARTMODIFIER = 8 - @DSTSTARTMODIFIER

    END

    ELSE

    BEGIN

        SET @DSTSTARTMODIFIER = (@DSTSTARTMODIFIER-1)*-1

    END

    -- This is the main calculation to determine the date of the MMWDHHmm pattern

    SET @DSTSTARTDT = DATEADD(day,(@DSTSTARTINSTANCE*7)-(8-@DSTSTARTDAY) + @DSTSTARTMODIFIER,@DSTSTARTDT)

    SET @DSTSTARTDT = DATEADD(hour,@DSTSTARTHOUR,@DSTSTARTDT)

    SET @DSTSTARTDT = DATEADD(minute,@DSTSTARTMINUTE,@DSTSTARTDT)

    -- Declare variables needed to convert MMWDHHmm pattern to for DST stop date

    DECLARE @DSTSTOPMONTH INT

    DECLARE @DSTSTOPINSTANCE INT

    DECLARE @DSTSTOPDAY INT

    DECLARE @DSTSTOPHOUR INT

    DECLARE @DSTSTOPMINUTE INT

    DECLARE @DSTSTOPMODIFIER INT

    -- Perform calculations to determine date

    SET @DSTSTOPMONTH = CONVERT(INT,SUBSTRING(@DSTENDDT,1,2))-1

    SET @DSTSTOPINSTANCE = CONVERT(INT,SUBSTRING(@DSTENDDT,3,1))

    SET @DSTSTOPDAY = CONVERT(INT,SUBSTRING(@DSTENDDT,4,1))

    SET @DSTSTOPHOUR = CONVERT(INT,SUBSTRING(@DSTENDDT,5,2))

    SET @DSTSTOPMINUTE = CONVERT(INT,SUBSTRING(@DSTENDDT,7,2))

    -- Set the start date variable to the first day of the year for the year of the date being evaluated

    SET @DSTSTOPDT = CONVERT(DATETIME,'1/1/' + CONVERT(VARCHAR(4),DATEPART(year,@NEWDT)))

    -- Determine the modifier value needed to adjust the date when the date desired is in a 

    SET @DSTSTOPDT = DATEADD(month,@DSTSTOPMONTH,@DSTSTOPDT)

    -- Determine the modifier value needed to adjust the date when the date desired is in a 

    -- month that does not start on a Sunday

    SET @DSTSTOPMODIFIER = DATEPART(weekday,@DSTSTOPDT)

    -- Check to see if the modifier date is greater than the date being evaluated

    -- This is important because it will change the final modifier

    -- (adding days in some instances and subtracting days in others

    IF @DSTSTOPMODIFIER > @DSTSTOPDAY

    BEGIN

        SET @DSTSTOPMODIFIER = 8 - @DSTSTOPMODIFIER

    END

    ELSE

    BEGIN

        SET @DSTSTOPMODIFIER = (@DSTSTOPMODIFIER-1)*-1

    END

    -- This is the main calculation to determine the date of the MMWDHHmm pattern

    SET @DSTSTOPDT = DATEADD(day,(@DSTSTOPINSTANCE*7)-(8-@DSTSTOPDAY) + @DSTSTOPMODIFIER,@DSTSTOPDT)

    SET @DSTSTOPDT = DATEADD(hour,@DSTSTOPHOUR,@DSTSTOPDT)

    SET @DSTSTOPDT = DATEADD(minute,@DSTSTOPMINUTE,@DSTSTOPDT)

    -- Check to see if the date being evaluated falls between the 

    -- DST start and stop date/times

    IF @DT BETWEEN @DSTSTARTDT AND DATEADD(hour,-1,DATEADD(second,-1,@DSTSTOPDT))

    BEGIN

        SET @NEWDT = DATEADD(hh,@DSTOFFSETHR*-1,@DT)

        SET @NEWDT = DATEADD(mi,@DSTOFFSETMI*-1,@NEWDT)

    END

    -- Return the new date that has been converted from UTC time

    RETURN @NEWDT

END

GO

CREATE FUNCTION [dbo].[TZTIME]

    (@DT AS DATETIME, 

     @TZ AS VARCHAR(12))

RETURNS DATETIME

AS

BEGIN

-- DECLARE VARIABLES

    DECLARE @NEWDT AS DATETIME

    DECLARE @OFFSETHR AS INT

    DECLARE @OFFSETMI AS INT

    DECLARE @DSTOFFSETHR AS INT

    DECLARE @DSTOFFSETMI AS INT

    --DECLARE @DSTDT AS VARCHAR(10)

    DECLARE @DSTEFFDT AS VARCHAR(10)

    DECLARE @DSTENDDT AS VARCHAR(10)

    DECLARE @DSTSTARTDT AS DATETIME

    DECLARE @DSTSTOPDT AS DATETIME

    -- This query gets the timezone information from the TIME_ZONES table for the provided timezone

    SELECT

        @OFFSETHR=offset_hr,

        @OFFSETMI=offset_mi,

        @DSTOFFSETHR=dst_offset_hr,

        @DSTOFFSETMI=dst_offset_mi,

        @DSTEFFDT=dst_eff_dt,

        @DSTENDDT=dst_END_dt

    FROM time_zones

    WHERE timezone_cd = @TZ AND

        @DT BETWEEN eff_dt AND end_dt

    -- Increase the datetime by the hours and minutes assigned to the timezone

    SET @NEWDT = DATEADD(hh,@OFFSETHR,@DT)

    SET @NEWDT = DATEADD(mi,@OFFSETMI,@NEWDT)

    -- Declare variables needed to convert MMWDHHmm pattern to  for DST start date

    DECLARE @DSTSTARTMONTH INT

    DECLARE @DSTSTARTINSTANCE INT

    DECLARE @DSTSTARTDAY INT

    DECLARE @DSTSTARTHOUR INT

    DECLARE @DSTSTARTMINUTE INT

    DECLARE @DSTSTARTMODIFIER INT

    -- Perform calculations to determine date

    SET @DSTSTARTMONTH = CONVERT(INT,SUBSTRING(@DSTEFFDT,1,2))-1

    SET @DSTSTARTINSTANCE = CONVERT(INT,SUBSTRING(@DSTEFFDT,3,1))

    SET @DSTSTARTDAY = CONVERT(INT,SUBSTRING(@DSTEFFDT,4,1))

    SET @DSTSTARTHOUR = CONVERT(INT,SUBSTRING(@DSTEFFDT,5,2))

    SET @DSTSTARTMINUTE = CONVERT(INT,SUBSTRING(@DSTEFFDT,7,2))

    -- Set the start date variable to the first day of the year for the year of the date being evaluated

    SET @DSTSTARTDT = CONVERT(DATETIME,'1/1/' + CONVERT(VARCHAR(4),DATEPART(year,@NEWDT)))

    -- Add month value for start pattern to the start date variable

    SET @DSTSTARTDT = DATEADD(month,@DSTSTARTMONTH,@DSTSTARTDT)

    -- Determine the modifier value needed to adjust the date when the date desired is in a 

    -- month that does not start on a Sunday

    SET @DSTSTARTMODIFIER = DATEPART(weekday,@DSTSTARTDT)

    -- Check to see if the modifier date is greater than the date being evaluated

    -- This is important because it will change the final modifier

    -- (adding days in some instances and subtracting days in others

    IF @DSTSTARTMODIFIER > @DSTSTARTDAY

    BEGIN

        SET @DSTSTARTMODIFIER = 8 - @DSTSTARTMODIFIER

    END

    ELSE

    BEGIN

        SET @DSTSTARTMODIFIER = (@DSTSTARTMODIFIER-1)*-1

    END

    -- This is the main calculation to determine the date of the MMWDHHmm pattern

    SET @DSTSTARTDT = DATEADD(day,(@DSTSTARTINSTANCE*7)-(8-@DSTSTARTDAY) + @DSTSTARTMODIFIER,@DSTSTARTDT)

    SET @DSTSTARTDT = DATEADD(hour,@DSTSTARTHOUR,@DSTSTARTDT)

    SET @DSTSTARTDT = DATEADD(minute,@DSTSTARTMINUTE,@DSTSTARTDT)

    -- Declare variables needed to convert MMWDHHmm pattern to  for DST stop date

    DECLARE @DSTSTOPMONTH INT

    DECLARE @DSTSTOPINSTANCE INT

    DECLARE @DSTSTOPDAY INT

    DECLARE @DSTSTOPHOUR INT

    DECLARE @DSTSTOPMINUTE INT

    DECLARE @DSTSTOPMODIFIER INT

    -- Perform calculations to determine date

    SET @DSTSTOPMONTH = CONVERT(INT,SUBSTRING(@DSTENDDT,1,2))-1

    SET @DSTSTOPINSTANCE = CONVERT(INT,SUBSTRING(@DSTENDDT,3,1))

    SET @DSTSTOPDAY = CONVERT(INT,SUBSTRING(@DSTENDDT,4,1))

    SET @DSTSTOPHOUR = CONVERT(INT,SUBSTRING(@DSTENDDT,5,2))

    SET @DSTSTOPMINUTE = CONVERT(INT,SUBSTRING(@DSTENDDT,7,2))

    -- Set the start date variable to the first day of the year for the year of the date being evaluated

    SET @DSTSTOPDT = CONVERT(DATETIME,'1/1/' + CONVERT(VARCHAR(4),DATEPART(year,@NEWDT)))

    -- Determine the modifier value needed to adjust the date when the date desired is in a 

    SET @DSTSTOPDT = DATEADD(month,@DSTSTOPMONTH,@DSTSTOPDT)

    -- Determine the modifier value needed to adjust the date when the date desired is in a 

    -- month that does not start on a Sunday

    SET @DSTSTOPMODIFIER = DATEPART(weekday,@DSTSTOPDT)

    -- Check to see if the modifier date is greater than the date being evaluated

    -- This is important because it will change the final modifier

    -- (adding days in some instances and subtracting days in others

    IF @DSTSTOPMODIFIER > @DSTSTOPDAY

    BEGIN

        SET @DSTSTOPMODIFIER = 8 - @DSTSTOPMODIFIER

    END

    ELSE

    BEGIN

        SET @DSTSTOPMODIFIER = (@DSTSTOPMODIFIER-1)*-1

    END

    -- This is the main calculation to determine the date of the MMWDHHmm pattern

    SET @DSTSTOPDT = DATEADD(day,(@DSTSTOPINSTANCE*7)-(8-@DSTSTOPDAY) + @DSTSTOPMODIFIER,@DSTSTOPDT)

    SET @DSTSTOPDT = DATEADD(hour,@DSTSTOPHOUR,@DSTSTOPDT)

    SET @DSTSTOPDT = DATEADD(minute,@DSTSTOPMINUTE,@DSTSTOPDT)

    -- Check to see if the date being evaluated falls between the 

    -- DST start and stop date/times

    IF @NEWDT BETWEEN @DSTSTARTDT AND DATEADD(hour,-1,DATEADD(second,-1,@DSTSTOPDT))

    BEGIN

        SET @NEWDT = DATEADD(hh,@DSTOFFSETHR,@DT)

        SET @NEWDT = DATEADD(mi,@DSTOFFSETMI,@NEWDT)

    END

    -- Return the new date that has been converted from UTC time

    RETURN @NEWDT

END

GO

CREATE FUNCTION [dbo].[NEW_TIME] 

    (@DT AS DATETIME, 

     @TZ1 AS VARCHAR(12),

     @TZ2 AS VARCHAR(12))

RETURNS DATETIME

AS

BEGIN

    -- Declare variables

    DECLARE @NEWDT AS DATETIME

    -- Check to see if the provided timezone for the source datetime is in GMT or UTC time

    -- If it is not then convert the provided datetime to UTC time

    IF NOT @TZ1 IN ('GMT','UTC')

    BEGIN

        SELECT @NEWDT = dbo.UTCTIME(@DT,@TZ1)

    END

    ELSE

    -- If the provided datetime is in UTC or GMT time then set the NEWTIME variable to this value

    BEGIN

        SET @NEWDT = @DT

    END

    -- Check to see if the provided conversion timezone is GMT or UTC

    -- If it is then no conversion is needed.

    -- If it is not then convert the provided datetime to the desired timezone

    IF NOT @TZ2 IN ('GMT','UTC')

    BEGIN

        SELECT @NEWDT = dbo.TZTIME(@NEWDT,@TZ2)

    END

    -- Return the new converted datetime

    RETURN @NEWDT

END

GO

MS SQL Time Zone Conversion Functions - revisited

A while back I wrote an article for code project regarding an implementation of some code that I wrote to replicate a time zone conversion process that mimics built-in processes in Oracle.  We had recently started migrating some of our databases from Oracle to MS SQL and needed to have this functionality to replicate some of the processes (I also created a blog entry for this).

I have come to use this function process a lot though I have come to realize that there are some performance hits associated to it that impacts large queries.  First off NEVER use this function in a where clause that includes a large number of records.  If this is needed it is better to create variables and use the function to create the appropriate value to match the time zone that the data is stored in and then use the variable(s) in the where clause.  This will drastically improve performance of the query.

Also in an effort to find other ways to improve performance I looked into alternate ways to implement the same features.  I delved into the area of .Net CLR functions for MS SQL to see if that improves performance.  I will be creating a how-to for three different types of CLR functions; CLR function where timezone info is stored in a SQL table, CLR function where the time zone info is maintained in a XML file, and a CLR function where the time zone info is hard coded in the code.

One thing to note is that I did not use the built-in c# timezone conversion functions because in order to do so we would have to mark the CLR functions as "Unsafe" in the database which is not something I am willing to do.  While researching I discovered the reason for this.  The reason is that those functions actually pull their timezone info from the Windows registry and as such is not safe to use since you do not want to expose registry access to normal functions.

Finally I will be providing some performance test information for each of the different functions to show the performance of each approach to know which would be best for you.

Timezone Conversion function using only Transact SQL
Timezone Conversion function using C# CLR function with configuration in SQL table
Timezone Conversion function using C# CLR function with configuration in XML file
Timezone Conversion function using C# CLR function with configuration hard coded in API
Performance evaluations of each approach

DreamForce 2011 - Day 2

Day two of DreamForce 2011 is done. It was a good and fully packed day. We started off the day with the keynote speach. Marc Benioff is a very good speaker and is very engaging to the crowd. As always they enveiled a few new things particularly in regards to their new social media strategy, data.com, a new visual interface for database.com, the official release of database.com....It was very informative but in my opinion it went a little too long. The end seemed to drag on so we left a little early.

My first session after lunch was on how to use SEO and SEM effectively. It was an interesting session. It was not particularly targeted towards my job responsibilities but there was nothing at that time that was and I think that SEO and SEM is interesting. I learned a lot of interesting things in regards to effectively setting things up for good indexing and such. If I have time I will post some of what I gleaned from that session. The presenters were excellent.

My second session was a hands on intro to the web services api. This session walked through some tutorials on how to create a java application that can consume Salesforce web services to present Salesforce content and update records in Salesforce but outside of Salesforce. It was a VERY brief tutorial. I would have liked a session though it was a bood breif introduction.

My third session was about developing web applications using jQuery. This is another one of those sessions that would have been more effective if it was hands on instead of an instructor walkthrough. It was good to see some of the possibilities of combining jQuery with our VisualForce pages though many in the room will most likely forget most of what was presented since there was no hands on. With that being said the walk though was actually a very good case and it was good to see the approach that they took to solve the casel.

I skipped my fourth session since I have already had two other Visual Process Manager sessions and I think that I am good with what it can do for us. Instead I went to the campground area and talked to some of the vendors that were there and got a few free things.

The day up to this point was really good but the highlight for me was going to the Gala. Metallica was the performer this year and they did an incredible job. They energy that they brought to the place was amazing. Last year was cool, Stevie Wonder is a classic and class act though to me this was absolutely incredible.

All in all it was a great day.

DreamForce 2011 - Day 1

Well DreamForce 2011 kicked off today. There were 44,000+ persons signed up for the event. It was something like I have never seen before. There are so many things going on that it is impossible for one person to relate how it is going and to describe the total experience. I can only relate my limited view of the event so far.

Being a developer and also having a specific business purpose for this event my view was limited to two aspects so far. My focus was to enhance my grasp of APEX and to learn more about how Visual Process Manager (or Visual Flow) works in Salesforce.

Today started out at the community conference. I was not expecting too much out of this. I attended it last year as well and for the most part it talked about the development plan of their MVP program and how great they were. Today it had that a little though one of the speakers also spoke quite a bit about the Radian6 aqcuisition and how that has impacted their view of how they evaluate social perception of their company. It was very interesting and opened my eyes as to some of the possibilities out there in social media aggregation.

My second session was a user experience design session. This was one of the coolest sessions I have attended at a conference. This was led by one of the systems analysts and the director of user experience. It was interesting to hear their new approach to development. They grouped us by tables and gave us some scenarios and had us work together to come up with potential solutions to the problem. One of their focus for this was to take technology out of the mix all together. Remove the "roles" that we put on ourselves (I'm a designer, a developer, a PM) and instead grab the paper and pin and just start collaborating ideas as a whole. Come up with something that can meet the needs of the issue and once that is done then move back into our roles to implement the solution. Of course this session only focused on the brain storming part of the process. It was very fun.

Next came lunch. Since I do not like mayo the lunch was pretty much worthless to me. So I nibbled on a few things.

Third session was about Visual Process Manager. This a primarily for business users. It was good. The focus was to show business persons how easy it is to use the tool and that with this we are able to put the builing of the process into the hands of the business where it should be. They are not trying to take IT out of the picture. There are elements where IT is needed, like when it comes to connecting to sources outside of the environment, but that the business should own the process and only pull in IT when they are needed. The presenter did a great job.

Session four was a hands on exersize in AJAX code development. We played around with creating a class that consumed emails coming into a custom email connector and parse the body to use to insert infomation into a Salesforce object. It was a short session but it opened my eyes into some of the possibilities and showed me the solution to a couple of solutions we were looking for.

Session five was called a hands on to Visual Process Manager though was more a visual demonstration. When I hear "hands on" I think that we will get to do some playing around. Instead the instructor was the one doing the playing around. Even though this was not what I expected it was a pretty good session. Earlier the focus of the meeting I was in was to the business. This session was focused towards the IT side of things. He showed a mock up of a business process that someone in the business had put together as well as the sections that were incomplete due to need for IT assistance. We have been using the old version of Visual Process Manager called FireFly (by the former Informavores) which includes an installed designer. For the demos today they showed off the new web-based designer that is planned for release in the Winter release (safe harbor statmenet goes here). This session was also very informative and enjoyable.

After the fifth session I headed over to the expo floor where there were vendors all over the place and some food. The amount of people in one place was astounding. People were everywhere. The most frustrating part of this is that the majority of the drinks available were alcoholic. I do not drink and it was not easy trying to find a drink that I wanted. When I asked for a mountain dew the server looked at me kind of funny and had to ask someone else to find the sodas. Even then he came back with a "Mist".

We only spent a little time there because there were too many people and we wanted to go to a party at the Museum of Modern Arts. There were a lot of people there as well but not as bad as the expo. The food there was really good though the drink situation was a little worse. The only non-alcoholic drink they had was soda-water. Interestingly enough they had a "candy bar". This was cool. We also spent time touring the art upstairs. Some of the things that they consider art is absolutely crap in my opinion. There was some are that I really enjoyed. After a while though I was tired and had to go back. I went back to the hotel, checked my email, did some work and then a couple of blogs. Now I am going to bed to get ready for another day tomorrow.

Creating an APEX trigger in Salesforce.com

To start off the first day of DreamForce 2011 I am posting a "how to" on creating an APEX trigger in Salesforce.com.  Recently the business approached me with a need.  Currently we get leads from a vendor via the web-to-lead process.  This process works great but the business wanted a way for the leads that are created to show up in the associated agent's calendar.

In scoping out how to meet the need of the business I had to research what fields the vendor is populating and what fields are required and so forth.  First off, I determined that the best process to display an activity in the agent's calendar was to create a trigger on the lead object that can create an entry into the event object.  The fields that the vendor is populating that would provide value to this process are: FirstName, LastName, remote_Rep_Assigned__c (custom field that the vendor puts the name of the assigned agent and must match the name of a Salesforce user), LeadSource, Appointment_Date_Time__c (custom field that the vendor puts the event date/time in UTC time), and company.

Requirements:
- Setup the trigger to only create leads when the LeadSource has a specific value.
- Query the users object to match the provided name of the agent provided to by the vendor to a Salesforce user
- If a Salesforce user match is not found do not create an event
- The Appointment_Date_Time__c field is not required, if this field does not have a value do not create an event

This is a basic example of creating an APEX trigger which may be a good start in learning the basics for more advanced coding.

There are a few ways to go about coding, and testing your code.  My preferred method is to use the eclipse IDE with the force.com addin.  You can see instructions on the different methods currently available (http://wiki.developerforce.com/index.php/Force.com_IDE_Installation) (though in DreamForce today we saw the new web-based APEX code interface).  You will also either need to create a force.com developer account (free) or if you have an enterprise license create a sandbox for development.  You will not be able to code directly to your production site for security and performance purposes.

Parts of the instructions will be specific to eclipse though the Force IDE has a similar process.  Open eclipse, go to File->New->Project.  Open the Force.com folder and select "Force.com Project" and click next.  For the "Project Name" I entered "CreateEventFromLead".  You will need to know your user name, password and security token along with selecting the appropriate account type.  If your login information is correct you should get a screen to allow you which meta data objects to download (this is needed to help with progamming).  The default should be "APEX and VisualForce" leave this marked and click "Finish"  The interface will take a few moments to download the necessary meta data and setup the work space.

Once the desktop is created, you should see a new package in the "package explorer" tab called "CreateEventFromLead" (or what ever name you named it).  Open the folder called "src".  Right click on the "triggers" folder and select Force.com->New Apex Trigger.  You will see the trigger configuration window.  I gave the trigger the name of CreateEventFromLead, in the object drop down selected Lead and selected the "After Insert" option (we only want this trigger to trigger after a new lead is created).

You should now have a new sheet with the shell of the trigger statement in it.  Now starts the fun part.  One thing to note is that it is possible for a trigger to be processed for multiple leads at the same time.  This is very different if you are use to the normal RDMS environments where a trigger and ran for each insert statement.  In Salesforce for batch jobs a trigger could process multiple insert statements at the same time.  This is a performance and efficiency element.  Because of this we must setup the trigger to be able to process multiple items so a loop is needed.  We will start off with adding a "for" loop.  Our trigger statement should look similar to the following.

trigger CreateEventFromLead on Lead (after insert) {
 // Create a for loop to cycle through the number of leads inserted.
 //  Documention that I have come across says that typically only one record is
 //  inserted at a time except for bulk operations
 for (Lead lead : System.Trigger.new) {

 } // End of for (Lead lead : System.Trigger.new) statement
} // End of trigger CreateEventFromLead

Next I need to get the full name for the contact in the lead.  Because the FirstName field is not required I need to create a variable for this value and then perform some checks to get the info.  First I create a variable named "leadName" and then create an "if"/"else" structure to populate the variable based upon whether the FirstName field is populated.

 string leadName;
  // Check to see if the lead "name" value is null
  if(lead.FirstName == null) { // Check to see if the first Name value is null
   // If the First Name value is null the only put the last name
   //  into the variable
   leadName = lead.LastName;
  } else { // If the first and last name fields have a value then populate the variable with both values
   leadName = lead.FirstName + ' ' + lead.LastName;
  }

After this I needed to create a variable to store events records that we want to create so that we can insert them into the event object in Salesforce.

// Create a new variable to store events that need to be created
list<event> newEvents = new list<event>();
// Insert newEvents collection into salesforce.com
insert newEvents;

We now come to the first check.  We run a query to search for any user objects that match the agent name that the vendor provided in the Remote_Rep_Assigned__c field.  In between the variable declaration and the insert statement we will add the following code.

// Create a for loop to query salesforce to get a user record that matches the
  //  value in the Remote_Rep_Assigned field
  for (User user : [SELECT Id FROM User WHERE Name = :lead.Remote_Rep_Assigned__c LIMIT 1]) {
   // Create a variable to store the user Id value from the user record
   Id userId = user.Id;

   // If the userId variable has a value then continue with the code
   if(userId != null) {

   } // End of if(userId != null) statement
  } // End of for (User user... statement

We now come to the part to put in our business rules on when to create the event.  We should be to the part where we have a valid user.  Based upon the requirements from the business we only want create an event if the LeadSource has a certain value, for this exersize we will use the value of "Inbound".  We also have the rule to only create the event if there is a value in the Appointment_Date_Time__c.  Within the "If" structure to see if there is a userId we want to add the following.  First we create a boolean variable and set it to False.  We will use this variable while we go through our business rules to see if we want to create the event.

// Create a variable to set whether an event should be created based upon the collowing conditions
    boolean createEvent = false;

    // This is the condition logic to determine whether an event should be created
    //  If an additional conditions should be created use the following template
    //  Replace the pattern [value] with the value of the lead source that should be pulled into this process
    // ***********
    // else if(leadSource == '[value]') {
    //   createEvent = true;
    // }
    // ***********
    if(leadSource == 'Inbound') {
     // Set createEvent variable to true which will cause the event to be generated
     createEvent = true;
    } // End of if(leadSource == 'West') statement

    if(lead.Appointment_Date_Time__c == null) {
     createEvent = false;
    } // End of if(lead.Appointment_Date_Time__c == null) statement

If our lead object passes all the business rule checks then we want to create the event.  First we put in an "if" structure to check our variable to see if the process should create the event.  If the checks are valid then we create a new event and assign some values.  I also added a try/catch structure just to make sure we do not get any errors (though we should have enough checks in place to not need this it is good to be on the safe side.

// If one of the conditions set the createEvent variable to True then execute the code to create the event
    if(createEvent) {
     // With the newEvents variable that was setup to store new events
     //  We call the "add" function. This function takes a parameter for an event variable
     //  We create a new event variable within the add function call.  The new
     //  Event variable creation process takes parameters to populate certain values with values
     //  that we pass to it.
     try {
      newEvents.add(new Event(
       // Pass the Id value for the user that matched what the third party sent to the owner Id
       //  This will assign the event to the user so that it shows up on their calendar in salesforce.com
       OwnerId = userId,
       // Pass the appointment datetime value passed from the third party in the lead
       //  to the event's date/time value
       ActivityDateTime = lead.Appointment_Date_Time__c,
       // Pass the Id of the lead to the WhoId value for the event.  This will assign the event
       //  to the lead so that it shows up in the lead view as well as allowing the lead info to be
       //  visible in the event details
       WhoId = lead.Id,
       // Passes the duration value of the event.  The third party does not pass a value
       //  so we are defaulting this value to 60 minutes
       DurationInMinutes = 60,
       // Passes a custom value into the subject line of the event to provide needed details
       //  for the owner of the event
       Subject = 'Call ' + leadName + ' with ' + lead.Company
       ) // End of new event
      ); // End of newEvent add funciton
     }
     catch(Exception e){
      system.debug(e + ' user id:' + userId + ' DateTime:' + lead.Appointment_Date_Time__c + ' lead Id:' + lead.Id);
     }
    } // End of if(CreateEvent) statement

This the completion of the trigger code.  Now comes the pain.  In order to be able to prepare the code for production we have to create test cases the cover at least 75% of the code.  There is a lot of information out on the web on how to do this but I still had a hard time with it.  I did a lot of testing and playing around until I was able to come up with test cases that would work.  In fact I spent more time on the test cases than I did on the actual code.

First, you right click on the "classes" and select Force.com->New APEX Class.  Give it a name, I called it ConvertEventFromLeadTest and gave it the template "Test Class".  This will create the class shell and include the @isTest assignment to the class.  The purpose of this test class is to pass values to the trigger to make sure that everything test out.  You should try to make it as dynamic as possible so that it can fit any scenario.  This class has the comments in the code sufficient to explain what is going on.  The big element was creating a loop for a controlled number of test records that would force the LeadSource to be the one we want in some situations and under others to pick one of the other defined LeadSources.

@isTest
private class CreateEventFromLeadTest {
    static testMethod void myUnitTest() {
        // Perform our data preparation.
     List<lead> leads = new List<lead>{};
     List<schema.picklistentry> leadSources = Lead.LeadSource.getDescribe().getPicklistValues();
     User user = [SELECT Id, Name FROM User WHERE IsActive = true LIMIT 1];
     string userName = user.Name;
    
     Integer iPicklist = 0;
    
     for(Schema.Picklistentry pick : leadSources) {
      iPicklist++;
     }
    
     Integer leadSourceCounter = 0;
     for(Integer i = 0; i < 60; i++){
      Integer days = Math.round(Math.random()*10.0);
    
      date dt;
      date tday = date.today();
    
         Lead l = new Lead();
         if(i<10) {
          l.FirstName = 'test';
          l.LastName = 'lead ' + i;
          l.LeadSource = 'Inbound';
         }
         else {
          l.LastName = 'test lead ' + i;
          l.LeadSource = leadSources[leadSourceCounter].getLabel();
         }
         Integer remainder = math.mod(i, 2);
         if(remainder==0) {
          dt = tday.addDays(days);
         }
         else {
          dt = null;
         }
         l.Email = 'test' + i + '@test.com';
        
         if(leadSourceCounter > iPicklist-1) {
          leadSourceCounter = 0;
         }
         l.Remote_Rep_Assigned__c = userName;
         l.Appointment_Date_Time__c = dt;
         l.Company = 'test company ' + i;
         l.Description = 'This Lead is probably left over from testing. It should probably be deleted.';
         leads.add(l);
        
       leadSourceCounter++;
     }

     // Start the test, this changes governor limit context to
     // that of trigger rather than test.
     test.startTest();
 
     // Insert the Lead records that cause the trigger to execute.
     insert leads;
 
     // Stop the test, this changes limit context back to test from trigger.
     test.stopTest();
 
     // Query the database for the newly inserted records.
     List<lead> insertedLeads = [SELECT FirstName, LastName, Description
                                       FROM Lead
                                       WHERE Id IN :leads];
 
     // Assert that the Description fields contains the proper value now.
     for(Lead l : insertedLeads){
       System.assertEquals(
         'This Lead is probably left over from testing. It should probably be deleted.',
         l.Description);
     }
 
     // Query the database for the newly inserted records.
     List<event> insertedEvents = [SELECT Subject
                                       FROM Event
                                       WHERE WhoId IN :leads];
 
     // Assert that the Description fields contains the proper value now.
     for(Event e : insertedEvents){
       System.assert(true);
     }
    }
}

Once the test class is done, you can right click on the class and select Force.com->Run Tests.  This will run the test cases against the trigger and provide all the results.  If everything is successful you are now ready to package the code for production.