March 5th was Data Platform WIT Day, a day of sessions by women in the data platform community. If you missed it, you can catch all of the sessions here. I had the honor of being part of the mentoring panel. I really enjoyed being on this panel with these women: Leslie Andrews (t|b), Gilda Alvarez (t), Deepthi Goguri (t|b), and Shabnam Watson (t|b) . All of us came here from different backgrounds and experiences and our careers have been affected in so many different ways from formal and informal mentorships.
There is a phrase that Rie (Irish) Merritt (t) says often: “Lift as you climb”. (It’s in her Keynote from March 5th.) This about finding ways to bring women along as you build your career to help them build theirs. This is something that I believe in but I feel like I constantly fail short in. I want to be someone who doesn’t just “talk the talk” but also “walks the walk.”
I seem to get drawn into the topic of mentorship over the past couple of years so perhaps it’s a sign that this is an area where I can be useful and help other women in our industry. Maybe this is a way I can help “pay it forward”.
This month is hosted by Brent Ozar (t | b). Brent asked us talk about our favorite or least favorite data type this month. You can check out the invitation here.
I never thought about whether I had a favorite data type. I mean, I’m a DBA so naturally, my favorite data type depends on the data.
But I think I’ll “wax poetic” about datetimes and all of the varieties. When I first got started with SQL Server back in the SQL 6.5 days, we really just had datetime, which was the date and time. If you needed just the time, you ignored the date portion. If you needed just the date, you put midnight (00:00:00.000) for the time. And when you dealt with the time portion, you deal with rounding to the .003 millisecond.
Now, we have all sorts of options – datetime, smalldatetime, datetime2, datetimeoffset, date, and time. The problem is when to use each of these.
The easiest ones are date and time. Now if you just need the date portion, you just use the date data type and you don’t have to deal with midnight or ignore the time portion of the datetime. If you just need the time, again you don’t have to use some random substitute date or ignore the date portion because you can use the time data type. Dates can hold any calendar date from January 1, 0001 through December 31, 9999 and Time can hold any time from 00:00:00.0000000 through 23:59.59.9999999
It starts get tricky from here. If you have to deal with multiple time zones, datetimeoffset is going to be your best bet. But then you’re having to deal with time zones, and we know how well we deal with those…. or rather don’t deal well with them. *Checks calendar and see Daylight Savings for the US coming in a few days…*
So now we’re left with smalldatetime, datetime, and datetime2. If you’re dealing with temporal tables, SQL Server says you have to use datetime2 for the system start and end times. Otherwise datetime2 is really a combination of date (all dates) and time (all times out to nanoseconds). Smalldatetime can hold any date from January 1, 1900 through June 6, 2079 (really – June 6??) and the time portion is accurate to the second. Datetime can hold any date from January 1, 1753 through December 31, 9999 and times out to the millisecond.
The fun really starts when you realize that what SQL Server stores internally isn’t what we see. We just see a formatted date time based on the default of the system or whatever we have chosen to have the format be if we specified the format somewhere. Datetime and smalldatetime can be converted to integers, but it looks like these values can be converted to varbinary. What’s even cooler is you can start to see how these different data types are related, regardless of the formatting that gets pulled back.
Query to set the different dates to the current datetimeThe results of the previous query.
Which date to choose really depends on the dates and times you need to store and how exact you need to be. You need to understand your data and what you are really trying to capture. The problem of course is when you are stuck with legacy systems so you can’t replace that datetime field with a date because that truly is all that you need.
But if I had to choose, what’s my favorite date? Well, I’ll just leave you with this:
Thanks for the great topic, Brent! Can’t wait to read what data types everyone else likes or dislikes…
Deb the DBA 