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: