Sql Server: How to Compare Dates
This article describes how to compare only the date portion of
datetime values.
Suppose we have a table of appointments with a column defined as a datetime data type:
CREATE TABLE [dbo].[Appointments]
(
[AppointmentId] [bigint] NOT NULL,
[ClientName] [varchar](50) NOT NULL,
[AppointmentDate] [datetime] NOT NULL
) ON [PRIMARY]
We want to select all apppointments with today's date ignoring the time of the apppointment.
If we use this SQL statement we will get a result set of zero (0) records.
Why? Because the comparision in the WHERE clause is comparing the date AND time
of the appintment with the current date AND time.
SELECT * FROM Appointments WHERE AppointmentDate = GETDATE();
We need a scalar-valued function to extract the date portions of the datetime value.
This function takes as input a datetime value, extracts the date portions (day, month, year)
from the input datetime value, constructs a date string, converts the date string into
a binary datetime, and returns the value to the caller.
CREATE FUNCTION [dbo].[DateGet]
(
@DateIn datetime
)
RETURNS datetime
AS
BEGIN
DECLARE @DateOut datetime
DECLARE @DateString varchar(40)
DECLARE @Day int
DECLARE @Month int
DECLARE @Year int
SET @Day = DATEPART( day, @DateIn)
SET @Month = DATEPART( month, @DateIn)
SET @Year = DATEPART( year, @DateIn)
SET @DateString = LTRIM(STR(@Year)) + '-' + LTRIM(STR(@Month)) + '-' + LTRIM(STR(@Day))
SET @DateOut = CONVERT(datetime, @DateString)
RETURN @DateOut
END
We use this function like this to return a result set with all of today's appointments:
SELECT * FROM Appointments WHERE dbo.DateGet(@AppointmentDate) = dbo.DateGet(GETDATE());
|