High-Performance Development - C#, VB, SQL, ASP.NET

Slogan Text

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 

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.

	@DateIn datetime
RETURNS datetime
	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

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());