T-SQL Tuesday in Local Time

The January edition of T-SQL Tuesday is next week! If you knew that, then you probably already know that the start and end times for T-SQL Tuesday are always at midnight UTC time, and that finding the current UTC date in SQL is as simple as using the GETUTCDATE() function.

SELECT GETUTCDATE();

You can obviously compare the two, but for any T-SQL Tuesday, the questions I really want answered are: “When does it start (in local time)?” and “How much time do I have before it starts and until it ends?”Get Moving! The script below will answer those questions.

This script runs for the current month, and assumes that T-SQL Tuesday is on the second Tuesday of the month (which it *usually* is). And for obvious reasons, it will only work on SQL 2008 and later.

Just now, running it now on my machine gave the following output:

Local time now: Jan 5 2012 8:09AM
The start of T-SQL Tuesday for this month is Jan 9 2012 6:00PM and the end is Jan 10 2012 6:00PM local time.
You have 4 days, 9 hours, 50 mins, and 53 seconds before it starts. Better get moving!

DECLARE 
	@Now DATETIMEOFFSET(0), 
	@TZ INT,
	@FDOM DATETIMEOFFSET(0), 
	@2ndTues DATETIMEOFFSET(0), 
	@TuesStart AS DATETIMEOFFSET(0), 
	@TuesEnd AS DATETIMEOFFSET(0),
	@Msg VARCHAR(MAX);
	
-- Save the current datetime and offset	
SELECT @Now = 
	SYSDATETIMEOFFSET(), @TZ = DATEPART(TZ,SYSDATETIMEOFFSET());

-- Get the first day of the current month (in UTC)
SET @FDOM = 
	DATEADD(dd, DATEDIFF(dd, 0, DATEADD(DAY, -1 * (DATEPART(DAY,@Now) - 1), @Now)), 0) ;

-- Get the second Tuesday of the current month and start/end datetimes (in UTC)
SET @2ndTues = 
	DATEADD(DAY, (7 + (10 - DATEPART(weekday,(DATEADD(DAY, 7, @FDOM)))) % 7), @FDOM);
SELECT @TuesStart = CAST(CAST(@2ndTues AS DATE) AS DATETIMEOFFSET(0)),
	@TuesEnd = CAST(CAST(DATEADD(day, 1, @2ndTues) AS DATE) AS DATETIMEOFFSET(0));

-- Display T-SQL Tuesday datetimes in local time	
SET @Msg = 'Local time now: ' + CAST(CONVERT(DATETIME, @Now) AS VARCHAR);
PRINT @Msg;
SET @Msg = 'The start of T-SQL Tuesday for this month is ' 
	+ CAST(CONVERT(DATETIME, SWITCHOFFSET(@TuesStart, @TZ)) AS VARCHAR) 
	+ ' and the end is ' + CAST(CONVERT(DATETIME, SWITCHOFFSET(@TuesEnd, @TZ)) AS VARCHAR) 
	+ ' local time.';
PRINT @Msg;

-- Countdown stats
IF @Now < @TuesStart
BEGIN
	SET @Msg = 'You have ' + CAST(DATEDIFF(day, @Now, @TuesStart)-1 AS VARCHAR) + ' days, ' 
		+ CAST((DATEDIFF(hour, @Now, @TuesStart) % 24)-1 AS VARCHAR) + ' hours, ' 
		+ CAST((DATEDIFF(minute, @Now, @TuesStart) % 60)-1 AS VARCHAR) + ' mins, and ' 
		+ CAST((DATEDIFF(second, @Now, @TuesStart) % 60) AS VARCHAR) 
		+ ' seconds before it starts. Better get moving!';
	PRINT @Msg;
END
ELSE IF @Now >= @TuesStart AND @Now <= @TuesEnd
BEGIN
	SET @Msg = 'You have ' + CAST(DATEDIFF(day, @Now, @TuesEnd)-1 AS VARCHAR) + ' days, ' 
		+ CAST((DATEDIFF(hour, @Now, @TuesEnd) % 24)-1 AS VARCHAR) + ' hours, ' 
		+ CAST((DATEDIFF(minute, @Now, @TuesEnd) % 60)-1 AS VARCHAR) + ' mins, and ' 
		+ CAST((DATEDIFF(second, @Now, @TuesEnd) % 60) AS VARCHAR) 
		+ ' seconds before T-SQL Tuesday ends. Publish!';
	PRINT @Msg;
END
ELSE IF @Now > @TuesEnd
	PRINT 'T-SQL Tuesday is over for this month.  See you next month!';

Confession: I really did this as an excuse to learn about some of the new date functionality in SQL 2008+ that I haven’t had a chance to use yet like DATETIMEOFFSET datatype and the SYSDATETIMEOFFSET() funciton, but hopefully it’s helpful as well.

Thanks, and remember, the clock is ticking!

This entry was posted in Uncategorized. Bookmark the permalink.

One Response to T-SQL Tuesday in Local Time

  1. Pingback: Tsql now | Miranda1929

Leave a Reply

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