SQLServer Date functions

getdate()
Return current date

datepart()
Get part of the date (Year, Month, Day, etc...)

Example for datepart
SELECT 
       datepart(year,getdate()) as year, 
       datepart(month,getdate()) as month, 
       datepart(day,getdate()) as day,       
       CAST(datepart(hour, getdate()) as varchar) + ':00' AS hour
       
year month day  hour
---- ----- --- -----
2015  4   2 15:00

datediff()
Get difference between dates.
select 
       DATEPART(year,getdate()) as year, 
       DATEPART(month,getdate()) as month, 
       DATEPART(day,getdate()) as day,       
       CAST(DATEPART(hour, getdate()) as varchar) + ':00' AS hour,
count(*) as emails
from dbo.email_message
where DATEDIFF(minute,convert(datetime,insert_date,100),getdate()) <60

year month day  hour emails
---- ------ --- -----  ------
2015 4 2 15:00 2

dateadd()
Add delta to a date
==========================
Select last day records
==========================
SELECT * 
FROM table_name
WHERE table_name.the_date > DATEADD(day, -1, GETDATE())

Get Future Date:
DATEADD(Month, 1, GETDATE())

convert()
Can be used to convert varchar to date or date to varchar.

SELECT * 
FROM table_name
and CONVERT(VARCHAR(7), table_name.the_date1, 111) between '2012/08' and '2012/09'

For example:
CONVERT(NVARCHAR(20), dtInsertDate, 104) => 04.03.2004   //DD.MM.YYYY
CONVERT(NVARCHAR(20), dtInsertDate, 114) => 14:43:36:000 //hh24:mi:ss:msec

===========================
Reference
===========================
List of CAST and CONVERT options:
CAST and CONVERT (Transact-SQL)