Trunc Date in SQL Server

I needed to truncate time out of a date field in SQL server but couldn't find an easy way to do this like in Oracle (Trunc Function), so this turned out to be the easiest way for me.

Create a Scalar Function in SQL Server:


CREATE FUNCTION [dbo].[Trunc] ( @pInputDate    DATETIME )
	RETURNS DATETIME
	BEGIN
	    RETURN CONVERT(VARCHAR(10), @pInputDate, 101)
	END

You might want to change 101 to match your locale.

And this is the way to use it:


select * users
where dbo.Trunc(registration_date)
between dbo.Trunc('01/01/2011') AND dbo.Trunc('01/31/2011');

Note: You might not know how the date is being passed (hopefully you're taking care of through an interface (date picker, etc); I just added dbo.Trunc to dates in 'Between' statement for this sample.

And That's it!

Trunc Date in SQL Server

Categories