Retrieving Only the Date from a Datetime Value in SQL Server

Retrieving Only the Date from a Datetime Value in SQL Server

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
There are many times where you might need to only retrieve the date from a datetime value.

For example, you may have today’s datetime (2011-10-10 20:30:28.230) and you want to only get the following value: 2011-10-10.

In SQL Server 2008 or later, you can easily do this by casting/converting the datetime value to the datatype DATE.

A typical example is the following:

–Datetime variable declaration
DECLARE @dateTimeValue as datetime
SET @dateTimeValue=GETDATE()
–Cast the datetime value to the DATE datatype
SELECT CAST(@dateTimeValue as DATE) as OnlyDate
GO

However, in earlier versions of SQL Server the DATE type is not available.
So, if you have a SQL Server 2005 instance or earlier and you want to get the date value from a datetime value you can just create and use a simple scalar-valued function like the following:


———————————
— Create scalar-valued function –
———————————
CREATE FUNCTION dateOnly
(
@dateInput datetime
)
RETURNS varchar (10)
AS
BEGIN

declare @tempRes as varchar(10)
declare @tempYear as varchar(4)
declare @tempMonth as varchar(2)
declare @tempDay as varchar(2)

set @tempYear=(select cast ((datepart(yyyy,@dateInput)) as varchar(4)))
set @tempMonth=(select cast ((datepart(MM,@dateInput)) as varchar(4)))
set @tempDay=(select cast ((datepart(dd,@dateInput)) as varchar(4)))

set @tempRes=@tempYear+’-‘+@tempMonth+’-‘+@tempDay

RETURN @tempRes

END
GO
—————————–


Now that the function is created, the first example changes to:

–Datetime variable declaration

DECLARE @dateTimeValue as datetime
SET @dateTimeValue=GETDATE()
–Get only the date value by calling the scalar-valued function
SELECT dbo.dateOnly(@dateTimeValue) as OnlyDate
GO

…. and that’s it! 🙂

I hope you found the article useful!

Until next time!


My Latest Projects:


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.