Using SQL Server 2008
-- remove the time
SELECT CAST(GETDATE() AS date) -- 2009-07-12
-- remove the date
SELECT CAST(GETDATE() AS time) -- 08:46:25.8130000
Cast Date with No Time Using Convert
-- 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
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())
Cast Date With No Time Using Floor
-- 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)
