Time has been much on my mind lately, as I've been busy slicing data, preparing reports and dashboards depicting trends over time, monthly aggregations, yearly forecasts and more. There seemed no better time, therefore, to be doing a beginner-level presentation for a forthcoming SQL Saturday event, teaching others some of what I'd learned a lot about how SQL Server handles dates and times. Preparing such a session should take a matter of minutes, surely?
"Every second of this talk will be filled with nothing but useful time-based examples!" I exclaimed excitedly to Karla, my wife. "In fact," I cried, getting a little carried away "I will forego a PowerPoint deck altogether and just use SSMS Query Editor as the main presentation tool!" Karla, well able to resist my bouts of boisterous enthusiasm, sighed and gave me her usual weary look, aware that I tend to over prepare for presentations, and wildly underestimate how long it will take. Still, this time would be different!
My first thought was that without traditional PowerPoint slides, I needed some cool ASCII Art, depicting grandfather clocks, pocket watches and so on, to inject some visual flair, and offset the proliferation of formatted T-SQL.
I glanced at my own watch. It was late. How had digging out a few images taken that long?! Never mind, on to the examples. The remainder of that night, and several following, I spent coding. I created scripts for almost every date and time function and data type, covering conversions and fun calculations one can do with hours, minutes, seconds, milliseconds and nanoseconds in T-SQL. I downloaded and imported unique and interesting datasets to show these functions. I made stored procedures and added indexes to show left-side predicate functions causing index scans.
I was covering time in all its minute detail, but also running out of it. I worked in the car on the way to the event (thankfully Karla drove), putting together Excel PowerPivot and PowerView charts that made clever use of time and date functions. I continued work on the presentation after the speaker dinner, long into the evening. The day of the event, a mere 2 hours before I would be presenting this for the first time, I was ready.
I stood before the audience as they filtered into the room, having had no time to rehearse. Here I was about to give a Beginner level session on dates and time in T-SQL, with no idea about the timing of it. A quick review of the examples convinced me I would be finished in about 7 minutes. I had better linger on the "About me" slide for 15 minutes to kill some time, I thought, panicking. Oh wait, I don't have one because I scratched PowerPoint! I don't have enough examples. The audience is going to know this stuff already. Why did I waste my time and theirs?!
Desperate, I resorted to a joke. "What is the best time of day?" I asked the audience…"6:30 hands down." It got a laugh, had the required calming effect, and I began. 45 minutes into the 60 minute session, I was only two thirds of the way through the T-SQL code. I discreetly skipped an example. After 65 minutes, I realized that my Excel charts would have to wait for another time and another place, and wrapped up.
I sighed to myself. I'd over prepared again, got too bogged down in the minutia of minutes. Had I under delivered? Fortunately, I was buoyed by various audience members telling me how they had learned something they did not know, whether it was the sargability of date functions, or how and why to convert datetimes to integers. A friend of mine was lingering.
"How about you" I asked, "did you learn something new too?"
"Sure", he smiled, "I learned a new bad joke about time."
Rodney landrum (Guest Editor).