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:
[cc lang=”sql”]
— remove the time
SELECT CAST(GETDATE() AS date) — 2009-07-12
— remove the date
SELECT CAST(GETDATE() AS time) — 08:46:25.8130000
[/cc]

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.
[cc lang=”sql”]
— 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)
[/cc]

Cast Date with No Time Using Convert
Using convert you can remove the time for display purposes, usually for reporting or the front end.
[cc lang=”sql”]
— 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
[/cc]

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.

[cc lang=”sql”]
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())
[/cc]

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