Right on the heels of the recent SQL Server 2012 virtual online launch event (as in virtually not there :-), a proven formula of online success known as 24-hours of PASS is coming to a pc near you!  We’re a few days away to a conglomeration of non-stop 24 hours of online technical training by the industry’s leading SQL Server experts, presenting on SQL Server’s hottest and timely topics, and accessible right from your own workstation!  This real virtual venue of webinars is a world-wide global event that covers 24 consecutive 1-hour sessions across 10 SQL Server related technology tracks.  You can view all the 24-HOP sessions by schedule, or by track, by clicking on the highlighted links.

Where else can you learn in the local luxury of your own convenient coordinate,  with session tracks such as Application Development, Big Data, BI, Database Administration, Performance, Powershell, and Professional Development, not to mention, SQL Azure, and Virtualization too!

The learning starts on March 21, 2012 beginning at 00:00 GMT (that’s a March 20 evening start for the Americas). You can still Register Today, for this victorious virtual event!

Attendees from all around the globe will be logging on to catch one of these awesome sessions, so you need to get the timing just right not to miss your favorite topic and speaker!  Indeed, it can be a bit confusing, since all the sessions are listed by Greenwich Mean Time (GMT) or Coordinated Universal Time (UTC) time. (I am using GMT & UTC interchangeably, even though there is an inconsequential technical difference).

Time is indeed relative, so make sure you have the right time!  That means you need to do some calculations to synchronize to your local time zone.  Or, you could set your alarm clocks, but perhaps in the spirit of SQL Server, how about a helpful database, some T-SQL, and a spreadsheet? 

No time to put this all together?  Well, as a community courtesy, I am making available my own little system I slapped together, to make this whole “global community” event just a bit more accessible. As an alternative, the site does offer a chart, to make sure that you have the right time, here, which points to a 3^rd party site that will do the conversion for you. Of course, you will have to look up your sessions, get the associated UTC time, locate your city, and then set the date/time, and modify your calculation.  You’ll be 24-HOPping at light speed – NOT! 😛 Why bother with such a manual process, when you can solve this issue with a simply T-SQL query?  Read on to find out how.

I have a small downloadable database (SQL 2008), that contains all the information that you need for your personalized 24-Hours of Pass experience. If you don’t have a SQL Server 2008 instance, you can download the SQL24HOP Spreadsheet here. The database contains the Session Tracks, Names, Presenters, related URL’s, and of course, the session time.  The session time is stored in the Session_Time_GMT column in UTC/GMT time using the new data type time. SQL Server 2008 introduces a TIME data type which allows us to store the time without the date.  Bill Graziano, SQL MVP and current PASS President, has an article discussion on Using the TIME data type in SQL 2008.

So, by using the stored UTC time, we can easily figure out and return the UTC and your local time, by getting the offset in time zones.  We do this by calculating the time offset difference between the SQL Server functions GETUTCDATE() and GETDATE(), respectively. Via GETUTCDATE(), the current UTC time 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 . Once we have the local time offset, we can easily get the session info for your current local time.  To simply get your UTC time offset, you can run the following t-sql:

select datediff(hh,GETUTCDATE(),GETDATE())

To start your journey, go and download the 24-HOP DB, and restore it to your local SQL Server 2008 or above.  Once the database is available, you can query to your heart’s content, but here is some SQL sauce to help get you started:

Declare @timeOffSet Int     

Set @timeOffSet=CAST(datediff(hh,GETUTCDATE(),GETDATE()) as INT) ---- Figure --out the time difference between UTC and Local time

select CONVERT(varchar(15),DateAdd(hh, @timeOffSet, Session_Time_GMT),100) AS My_Local_Time,

DateAdd(hh, @timeOffSet, Session_Time_GMT) My_24Hour_Time,

Session_Time_GMT,

@timeOffSet AS My_Time_Offset,

Session_Number,

Session_Name,

Presenter,

Session_Track,

Presenter_URL,

Session_URL

from [24HOP_T]

The results should like similar here:

If you so desire, you can set up SQL alerts for where the current UTC time is equal to your local time, as a reminder for all your sessions, but I’ll leave that to your own devices.

Indeed, with a global audience in mind, this edition of 24 Hours of PASS will feature closed captioning in 15 different languages!  (see the site for idioma’s 🙂  And, as a side note, for the first time, they are offering a follow-up event for our Russian speaking comrades on April 12^th, 2012.

Thank you PASS, for continuing this online tradition and community contribution.  Thank you to the wonderful speakers lined-up, and look forward to all your special presentations.  So, wherever you are in the world, be sure to register and not miss this event!  No need to travel!

Spring is here, so let’s get hopping, with the special online event, 24-HOP! Enjoy your FREE training!  

You can follow me on Twitter @PearlKnows, or join me on LinkedIN.