Fun with dates

Every year me and a group of friends attend the Royal International Air Tattoo (RIAT) at RAF Fairford, for us it is a long weekend where we camp close to the airfield and get to take in three full days (if you include Friday practice  of utterly breath-taking aerobatic displays from some of the greatest military aircraft in the world.  We have a running joke during the year long wait between events that once we get Christmas out of the way RIAT will be here before we know it.  

We all use dates as DBA’s in fact we use them all the time, every single day even without realising it.  I often use T-SQL to show me the difference between dates, but this particular script I like because it gives me a countdown to RIAT, so on a bad day or even on a good day for that matter I can see just how long, or little time is left until we set off once again J

Here is a script I use for fun;

/*

      -----------------------------------------------------------------

      Fun with dates

      -----------------------------------------------------------------

    

      For more SQL resources, check out SQLServer365.blogspot.com

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

    

      -----------------------------------------------------------------

*/

-- Set database context

USE master;

GO

-- Declare variables

DECLARE @startTime DATETIME

DECLARE @endTime DATETIME

DECLARE @Seconds INT

-- Set date range

SET @startTime = GETDATE()

SET @endTime = '20130719 04:30'

-- Get the difference in seconds

SELECT  @Seconds = DATEDIFF(ss, @startTime, @endTime);

-- Return how long left                                                       

SELECT  'T Minus ' + CONVERT(VARCHAR(10), ( @Seconds / 86400 )) + ' Days '

        + CONVERT(VARCHAR(10), ( ( @Seconds % 86400 ) / 3600 )) + ' Hours '

        + CONVERT(VARCHAR(10), ( ( ( @Seconds % 86400 ) % 3600 ) / 60 ))

        + ' Minutes ' + CONVERT(VARCHAR(10), ( ( ( @Seconds % 86400 ) % 3600 )

                                               % 60 )) + ' Seconds and counting!';

GO

Enjoy!

Chris