Date comparison: SQL uses standard comparison operators to compare dates: <, >, =, <> or !=
Note: SQL compares dates chronologically, where earlier dates are considered "less than" later dates.
- Represent dates in standard format (YYYY-MM-DD)
- Use comparison operators to evaluate relationships
- Understand that earlier dates are less than later dates
SQL recognizes dates in formats like 'YYYY-MM-DD': '2023-01-15' represents January 15, 2023
'2023-01-15' < '2023-01-20' evaluates if January 15 comes before January 20
Since January 15 occurs before January 20, the result is TRUE
'2023-01-15' < '2023-01-20' = TRUE
• Date ordering: Earlier dates are less than later dates
• Standard format: YYYY-MM-DD ensures proper chronological comparison
• Comparison operators: Same as for numbers but applied chronologically
• Practice Tip: Think of dates as positions on a timeline: left (earlier) < right (later)
- '2023-02-01' > '2023-01-31' = TRUE - February comes after January
- '2023-03-10' = '2023-03-10' = TRUE - Same dates are equal
- '2022-12-31' < '2023-01-01' = TRUE - End of year before next year
- SQL automatically handles leap years and month lengths in date comparisons
- Always use consistent date formats to avoid errors
- Chronological thinking: earlier dates have "smaller" values than later dates
Q: Can I compare dates in different formats?
A: No, always use consistent formats. Most SQL databases expect YYYY-MM-DD format.
Q: What happens with invalid dates?
A: SQL will generate an error. Always ensure dates are valid (e.g., no February 30).
Greater than operator: > returns TRUE when the left date occurs after the right date chronologically
Note: This operator is commonly used to filter records that occurred after a certain point in time.
- Identify the reference date to compare against
- Use the > operator to find dates occurring later
- Structure query to return matching records
Define '2023-06-01' as the cutoff date for comparison
date_col > '2023-06-01' finds dates that occur after June 1, 2023
Any date after June 1, 2023 (like June 2, July 15, etc.) will satisfy the condition
This query returns all dates occurring after June 1, 2023
• Chronological order: Later dates are greater than earlier dates
• Filtering: Use WHERE clause to filter based on date conditions
• Practical use: Commonly used for finding recent records or future events
• Practice Tip: Remember: dates further right on timeline have greater values
- WHERE date_col > '2023-12-25' - Finds dates after Christmas 2023
- WHERE date_col > '2023-02-28' - Finds dates after end of February
- WHERE date_col > '2023-01-01' - Finds dates after New Year's Day
- Use greater than for "after" conditions: birthday > '2000-01-01' means born after 2000
- Combine with other operators for ranges: date > 'start' AND date < 'end'
- Think of timeline: dates to the right have greater values than those to the left
Q: What if I want dates on or after a specific date?
A: Use the greater than or equal operator: date_col >= '2023-06-01'
Q: Can I use this with time components?
A: Yes, datetime comparisons also work: '2023-06-01 10:30' > '2023-06-01 09:00'
Less than operator: < returns TRUE when the left date occurs before the right date chronologically
Note: This operator is commonly used to filter records that occurred before a certain point in time.
- Identify the reference date to compare against
- Use the < operator to find dates occurring earlier
- Structure query to return matching records
Define '2023-06-01' as the cutoff date for comparison
date_col < '2023-06-01' finds dates that occur before June 1, 2023
Any date before June 1, 2023 (like May 30, April 15, etc.) will satisfy the condition
This query returns all dates occurring before June 1, 2023
• Chronological order: Earlier dates are less than later dates
• Filtering: Use WHERE clause to filter based on date conditions
• Practical use: Commonly used for finding old records or past events
• Practice Tip: Remember: dates further left on timeline have lesser values
- WHERE date_col < '2023-01-01' - Finds dates before 2023
- WHERE date_col < '2023-02-28' - Finds dates before end of February
- WHERE date_col < '2023-12-25' - Finds dates before Christmas
- Use less than for "before" conditions: birthday < '2010-01-01' means born before 2010
- Combine with other operators for ranges: date > 'start' AND date < 'end'
- Think of timeline: dates to the left have lesser values than those to the right
Q: What if I want dates on or before a specific date?
A: Use the less than or equal operator: date_col <= '2023-06-01'
Q: Can I use this with time components?
A: Yes, datetime comparisons also work: '2023-06-01 08:00' < '2023-06-01 09:30'
Equal operator: = returns TRUE when both dates are identical
Note: This operator is used to find exact date matches, often for specific events or appointments.
- Specify the exact date to match
- Use the = operator to find exact matches
- Structure query to return matching records
Define '2023-06-01' as the exact date to match
date_col = '2023-06-01' finds records with exactly this date
Only records with the exact date June 1, 2023 will be returned
This query returns only records with the exact date June 1, 2023
• Exact matching: Only identical dates return TRUE
• Filtering: Use WHERE clause to filter based on exact date conditions
• Practical use: Commonly used for finding specific events or appointments
• Practice Tip: Remember: = means exact match, not range
- WHERE date_col = '2023-01-01' - Finds records on New Year's Day
- WHERE date_col = '2023-12-25' - Finds records on Christmas Day
- WHERE date_col = '2023-07-04' - Finds records on Independence Day
- Use equal for exact date matches: appointment_date = '2023-06-15'
- Combine with other conditions: date = '2023-06-01' AND status = 'active'
- Be precise with formatting: '2023-06-01' ≠ '2023-6-1' in most systems
Q: Does this work with datetime values?
A: Yes, but time components must also match exactly: '2023-06-01 10:00:00' = '2023-06-01 10:00:00'
Q: What if I want dates within a day range?
A: Use BETWEEN or combine with other operators: date >= 'start' AND date <= 'end'
Not equal operator: <> or != returns TRUE when dates are different
Note: This operator is used to exclude specific dates from results.
- Specify the date to exclude
- Use the <> or != operator to find non-matching dates
- Structure query to return all other dates
Define '2023-06-01' as the date to exclude from results
date_col <> '2023-06-01' finds all dates except June 1, 2023
All dates except June 1, 2023 will be returned
This query returns all dates except June 1, 2023
• Exclusion: Only different dates return TRUE
• Filtering: Use WHERE clause to exclude specific date conditions
• Practical use: Commonly used for excluding holidays or specific dates
• Practice Tip: Remember: <> and != are equivalent operators
- WHERE date_col != '2023-01-01' - Excludes New Year's Day
- WHERE date_col <> '2023-12-25' - Excludes Christmas Day
- WHERE date_col != '2023-07-04' - Excludes Independence Day
- Use not equal for exclusions: date_col != '2023-06-01' excludes that date
- Combine with other operators: date != 'holiday' AND date < 'end_date'
- Both <> and != work the same way in most SQL databases
Q: What's the difference between <> and != ?
A: There is no functional difference; both represent "not equal". != is more familiar to programmers.
Q: How does NULL affect not equal comparisons?
A: NULL values never equal anything, including themselves. Use IS NULL or IS NOT NULL for NULL checks.