sql_dates

Working with Dates and Times in SQL

Handling dates and times is a common task in SQL. The main challenge often lies in understanding the date/time data types available in your specific database system and using the correct functions to manipulate and query them.

	Key Consideration: Ensure the format of the date/time literal you use in queries matches what the database expects, or use explicit conversion/casting functions.

If your data contains only the date part, queries are often straightforward. However, when time components are involved, or time zones, queries require more precision.

Common SQL Date/Time Functions (ANSI SQL and Vendor-Specific)

While many date/time functions are database-specific, some concepts and functions are common or part of the SQL standard:

• CURRENT_DATE: Returns the current date.
• CURRENT_TIME: Returns the current time (often with time zone).
• CURRENT_TIMESTAMP or NOW(): Returns the current date and time (often with time zone). (NOW() is common but not strictly ANSI for all contexts).
• EXTRACT(<em>part</em> FROM <em>date_expression</em>): Extracts a specific part (e.g., YEAR, MONTH, DAY, HOUR) from a date/time value.
• Date Arithmetic: Adding or subtracting intervals (e.g., date_column + INTERVAL '7' DAY). Syntax for intervals varies.

Below are lists of important built-in date functions for specific database systems:

MySQL Date/Time Functions:

Function	Description
NOW()	Returns the current date and time.
CURDATE()	Returns the current date.
CURTIME()	Returns the current time.
DATE(expr)	Extracts the date part of a date or datetime expression.
TIME(expr)	Extracts the time part of a datetime expression.
EXTRACT(unit FROM date)	Returns a single part of a date/time (e.g., YEAR, MONTH, DAY).
DATE_ADD(date, INTERVAL expr unit)	Adds a specified time interval to a date.
DATE_SUB(date, INTERVAL expr unit)	Subtracts a specified time interval from a date.
DATEDIFF(expr1, expr2)	Returns the number of days between two dates.
DATE_FORMAT(date, format)	Formats a date/time value according to a format string.

SQL Server Date/Time Functions:

Function	Description
GETDATE() / SYSDATETIME()	Returns the current date and time (SYSDATETIME() offers higher precision).
DATEPART(datepart, date)	Returns a single part of a date/time as an integer.
DATENAME(datepart, date)	Returns a single part of a date/time as a string.
DATEADD(datepart, number, date)	Adds a specified time interval to a date.
DATEDIFF(datepart, startdate, enddate)	Returns the time between two dates, in the specified datepart.
CONVERT(datatype, expression, [style])	Converts an expression from one data type to another, often used for date formatting.
FORMAT(date, format, [culture])	Formats a date/time value (SQL Server 2012+).

SQL Date/Time Data Types

Choosing the correct data type for date and time information is crucial for storage efficiency and query performance.

MySQL Data Types:

• DATE: Stores date values in ‘YYYY-MM-DD’ format.
• DATETIME: Stores date and time values in ‘YYYY-MM-DD HH:MI:SS’ format. Range from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.
• TIMESTAMP: Stores date and time values. Range from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC. Values are stored in UTC and converted to/from the session’s time zone. Can auto-update on row modification.
• TIME: Stores time values in ‘HH:MI:SS’ format.
• YEAR: Stores year values in YYYY or YY format.

SQL Server Data Types:

• DATE: Stores date values in YYYY-MM-DD format.
• TIME: Stores time values in hh:mm:ss.nnnnnnn format.
• DATETIME2(n): Stores date and time with fractional seconds precision (n=0 to 7). Preferred over older DATETIME. Format: YYYY-MM-DD hh:mm:ss.nnnnnnn.
• SMALLDATETIME: Stores date and time, less precise (no fractional seconds, seconds are rounded). Format: YYYY-MM-DD HH:MI:SS.
• DATETIME: Older date and time type. Less precise and smaller range than DATETIME2.
• DATETIMEOFFSET(n): Stores date and time with time zone awareness.
• TIMESTAMP: Note: In SQL Server, TIMESTAMP (or ROWVERSION) is NOT a date/time type but a unique binary number that updates with row modifications, used for versioning.

Always consult your database system’s documentation for the most accurate and detailed information on data types and functions.

Querying Date and Time Values

	Comparing dates without time components is generally straightforward. Including time components requires careful handling.

Assume we have an “Orders” table with an OrderDate column.

Scenario 1: OrderDate column is of type DATE (stores only date part):

OrderId
ProductName
OrderDate


1
Geitost
2023-11-11


2
Camembert Pierrot
2023-11-09


3
Mozzarella di Giovanni
2023-11-11

To select orders placed on ‘2023-11-11’:

SELECT *
FROM Orders
WHERE OrderDate = '2023-11-11';

This query works as expected, returning orders 1 and 3.

Scenario 2: OrderDate column is of type DATETIME or TIMESTAMP (stores date and time):

OrderId
ProductName
OrderDate


1
Geitost
2023-11-11 13:23:44


2
Camembert Pierrot
2023-11-09 15:45:21


3
Mozzarella di Giovanni
2023-11-11 11:12:01


4
Mascarpone Fabioli
2023-10-29 14:56:59

If we use the same query as above:

SELECT *
FROM Orders
WHERE OrderDate = '2023-11-11';

This query will likely return no results (or behave unexpectedly depending on implicit conversions). This is because ‘2023-11-11’ might be interpreted as ‘2023-11-11 00:00:00’, which doesn’t match any OrderDate values exactly.

To correctly query for all orders on ‘2023-11-11’, regardless of the time:

Method 1: Using a date range (most reliable and often index-friendly):

SELECT *
FROM Orders
WHERE OrderDate >= '2023-11-11 00:00:00'
  AND OrderDate < '2023-11-12 00:00:00'; -- Up to, but not including, the next day

Method 2: Casting/Converting the OrderDate column to a DATE type (function availability and performance may vary):

-- SQL Server example
SELECT *
FROM Orders
WHERE CAST(OrderDate AS DATE) = '2023-11-11';

-- MySQL example
SELECT *
FROM Orders
WHERE DATE(OrderDate) = '2023-11-11';

Tip: Understand your date/time data types and always be explicit when querying date/time columns, especially when time components are involved. Using ranges or casting to DATE are common best practices.