SQL Cast Date

Using SQL Server 2008
This first option of removing the date uses the SQL Server 2008 method. This will return only the date or only the time:

-- remove the time
SELECT CAST(GETDATE() AS DATE) -- 2009-07-12
-- remove the date
SELECT CAST(GETDATE() AS TIME) -- 08:46:25.8130000

If you’re not working with SQL Server 2008, you have to improvise.  

Cast Date With No Time Using Floor
This example removes the time from the date time by setting it to the beginning of the day.

-- Get the current day with no time
-- 2009-06-29 00:00:00.000
SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS datetime)

-- Get the next day
-- 2009-06-30 00:00:00.000
SELECT CAST(CEILING (CAST(GETDATE() AS FLOAT)) AS datetime)

Cast Date with No Time Using Convert
Using convert you can remove the time for display purposes, usually for reporting or the front end.

-- Month first
SELECT CONVERT(VARCHAR(12),GETDATE(), 101)  -- 06/29/2009
SELECT CONVERT(VARCHAR(12),GETDATE(), 110)  -- 06-29-2009
SELECT CONVERT(VARCHAR(12),GETDATE(), 100)  -- Jun 29 2009
SELECT CONVERT(VARCHAR(12),GETDATE(), 107)  -- Jun 29, 2009

-- Year first
SELECT CONVERT(VARCHAR(12),GETDATE(), 102)  -- 2009.06.29
SELECT CONVERT(VARCHAR(12),GETDATE(), 111)  -- 2009/06/29
SELECT CONVERT(VARCHAR(12),GETDATE(), 112)  -- 20090629

-- Day first
SELECT CONVERT(VARCHAR(12),GETDATE(), 103)  -- 29/06/2009
SELECT CONVERT(VARCHAR(12),GETDATE(), 105)  -- 29-06-2009
SELECT CONVERT(VARCHAR(12),GETDATE(), 104)  -- 29.06.2009
SELECT CONVERT(VARCHAR(12),GETDATE(), 106)  -- 29 Jun 2009

-- Time only
SELECT CONVERT(VARCHAR(12),GETDATE(), 108)  -- 07:26:16
SELECT CONVERT(VARCHAR(12),GETDATE(), 114)  -- 07:27:11:203

Roll Your Own Date
To roll your own date format, use the datename function. This is also the only way to get the full month name.

SELECT DATENAME(MONTH, GETDATE())   -- June
SELECT DATENAME(DAY,GETDATE())   -- 29
SELECT DATENAME(YEAR, GETDATE())      -- 2009

-- Concatente values
-- June.29.2009
SELECT DATENAME(MONTH, GETDATE()) + '.' + DATENAME(DAY,GETDATE()) + '.' + DATENAME(YEAR, GETDATE())

Here are the rest of the possible datepart intervals:

Date Part Name Interval Values
Year year, yyyy, yy
Quarter quarter, q, qq
Month month, mm, mm
Day of the year dayofyear, dy, y
Day day, dd,. D
Weekday weekday, dw
Week week, wk, ww
Hour hour, hh
Minute minute, mi, n
Second second, ss, s


10 comments
ombro 30 Oct 2013 at 2:44 am

very useful

prabha 19 Aug 2013 at 7:26 am

very useful,thanks

suhas 23 Dec 2011 at 12:43 am

how to insert current date in table. in sql server 2005

Priyanka 14 May 2013 at 9:47 am

In older versions of SQL, you can use the following statements to get only Time and only Date

Only Time (hh:mm:ss)
==================
select CAST(CURRENT_TIMESTAMP AS TIME) from TBL

Only Date (yyyy-mm-dd)
====================
select CAST(CURRENT_TIMESTAMP AS DATE) from TBL

:)

mostafa 25 Sep 2011 at 1:22 am

very nice
Tank’s

saboor 08 Jun 2011 at 7:35 am

I have a problem
I have a datetime and i need the date to specific format

so I just casted datetime to time
SELECT CAST (GETDATE() AS DATE)

and for formatting i use convert
SELECT CONVERT(DATE,CAST (GETDATE() AS DATE),105)

but, the result of both is same,

convert is not working formating

Kosta Masalov 07 Jun 2011 at 9:19 am

Exactly what I was looking for, thanks!

INal 25 Jan 2011 at 10:17 pm

Thanks you. This is most helpful.

Dom 10 Oct 2010 at 5:09 pm

Thanks, perfect exactly what I need to Know

WILLIAM 16 Jul 2009 at 9:52 pm

thanks for the effort

Featured Articles

 Site Author