Format dates:

SELECT CONVERT(CHAR(10), GETDATE(), 111), -- yyyy/mm/dd   2012/06/19
       CONVERT(CHAR(10), GETDATE(), 1),   -- mm/dd/yy     06/19/12
       CONVERT(CHAR(10), GETDATE(), 101), -- mm/dd/yyyy   06/19/2012
       CONVERT(CHAR(19), GETDATE(), 120), -- yyyy-mm-dd   2012-06-19 12:32:51
       CONVERT(CHAR(10), GETDATE(), 107)  -- mmm dd, yyyy Jun 19, 20

Minimum and maximum date in SQL Server:

Minimum: 1/1/1753
Maximum: 12/31/9999

Cast a string to a date:

CAST('20050312' AS DATETIME)
CAST('17530101' AS DATETIME) -- min SQL date
CAST('99991231' AS DATETIME) -- max SQL date

Examples of built-in functions involving DATETIME:

DECLARE @DATE DATETIME
SET @DATE = CAST('20120314' AS DATETIME) -- 03/14/2012
 
-- Add 6 days
SELECT DATEADD(d, 6, @DATE) -- 03/20/2012
 
-- Get the week part
SELECT DATEPART(wk, @DATE) -- 12
 
-- Subtract one day
SELECT DATEADD(DAY, -1, @DATE)
 
-- Get date parts
SELECT YEAR(@DATE)
SELECT MONTH(@DATE)
SELECT DAY(@DATE) 
SELECT DATEPART(HOUR, @DATE)
SELECT DATEPART(MINUTE, @DATE)

Create DATETIME from year, month, and day parts:

DECLARE @DAY INT, @MONTH INT, @YEAR INT
SET @DAY = 18
SET @MONTH = 7
SET @YEAR = 2014
SELECT CAST(RTRIM(@YEAR*10000 + @MONTH*100 + @DAY) AS DATETIME) -- 2014-07-18 00:00:00.000
 
-- As a function:
CREATE FUNCTION fn_GetDate(@DAY INT, @MONTH INT, @YEAR INT) 
RETURNS DATETIME
AS
BEGIN
    RETURN CAST(RTRIM(@YEAR*10000 + @MONTH*100 + @DAY) AS DATETIME)
END
GO

Drop the time component from a DateTime:

CREATE FUNCTION fn_GetDateOnly(@DateTime DATETIME)
RETURNS CHAR(10)
AS
BEGIN
  RETURN CONVERT(CHAR(10), @DateTime, 101)
END
GO  
 
SELECT dbo.fn_GetDateOnly(GETDATE()) -- result: 12/17/2012