Dates In SQL Server
Some time ago what started as one article on dates and their value, turned into a mini-series (nearly as engaging as The Queens Gambit). I kept finding more to say about dates and examples to give. I’m now pulling them together with a little narrative. “One page to rule them all” as it were.
What’s so significant about dates?
We know they are valuable!
We all know that dates themselves are important, but what sometimes gets overlooked is the date datatype (and other date and time datatypes). When and how to use it, and how it can be leveraged.
Getting started
A good introduction, or at least a refresher on date-related datatypes.
- Dates In SQL Server: Managing & Manipulating Dates – Data types, usage and formatting for display.
- Dates In SQL Server: DATETIME2 vs SMALLDATETIME – A dive into two common (but different) datatypes used for storing date with time. For people new to looking into datatypes, it is a great demonstration of the subtle, yet significant difference between similar-looking data types.
- Dates In SQL Server: Adding & subtracting – Simple manipulation, but a real demonstration of the value of using the correct data type.
- Dates In SQL Server: Ambiguous date specification – Some of the dangers and pitfalls to be conscious of when working with dates.
Getting warmed up
Useful reading which the later articles build on (or reuse).
- Dates In SQL Server: Create Sample Date Ranges – Examples of ways to create a table of consecutive dates.
Steaming ahead
Specialised usage examples working with dates.
Iteration (looping over)
- Dates In SQL Server: Iteration of Date Ranges – Loop over a series of dates (logic can be changed to loop over anything, but dates are the most common in data warehousing).
- Dates In SQL Server: Creating Date Range Batches – This is a more complicated example and quite niche, after creating a solution using the approach I thought it was a great example to write about.
Categorisation & Identigication
- Dates In SQL Server: Weekdays & Weekends – Identifying repeated days which often impact processing or business peaks and troughs.
- Dates In SQL Server: Public Holidays – Identifying and managing information about holidays, the more you think about it the more complicated it can become.
- Dates In SQL Server: The last X of the month – This is a niche example, of how to identify specific days in a month (usually to identify business process points).
Partitions using Dates
A common theme in data warehousing is partitioning, either for disk usage or performance improvements. Often, the nature of data and time dates make ideal candidates for partitioning fact tables:
- Partitions in SQL Server: Creating a Partitioned Fact Table (by date)
- Partitions in SQL Server: Partition an Existing Fact Table (by date)
Dates in other databases
Although this page is about SQL Server, I’ve written equivalent notes on other databases: