SELECT 
    DAY('5/5/2007'), 
    MONTH('5/1/2007'), 
    YEAR('5/1/2007'),
    DATEPART(DAY, '1/5/2007'), 
    DATEPART(MONTH, '5/1/2007'), 	
    DATEPART(YEAR, '1/1/2007')

Day         MOnth       year        datepart_Day datepart_MOnth datepart_year
======-- ======-- ======-- ======--- ======----- ======----
5           5           2007        5            5              2007


SELECT 
    GETDATE() AS local_date, 
    GETUTCDATE() AS UTC_date

local_date              UTC_date
============----- ============-----
2007-05-08 19:12:50.687 2007-05-08 13:42:50.687

SELECT DATEADD(MONTH, 6, GETDATE())AS '6_months_from_now'

6_months_from_now
============-----
2007-11-08 19:12:50.687
==============================
declare @datevar datetime 
select @datevar = getdate()

/*Example for getdate() : getting current datetime*/ 
select getdate() [Current Datetime]

/*Example for dateadd : getting date 7 days from current datetime*/
select dateadd(dd, 7, @datevar) [Date 7 days from now]

/*Example for datediff : getting no of days passed since 01-01-2004*/
select datediff(dd,'20040101',@datevar) [No of days since 01-01-2004]

/*Example for datename : getting month name*/
select datename(mm, @datevar) [Month Name]

/*Example for datepart : getting week from date*/
select datepart(wk, @datevar ) [Week No] 

/*Example for day : getting day part of date*/
select day (@datevar) [Day]

/*Example for month : getting month part of date*/
select month(@datevar) [Month]

/*Example for year : getting year part of date*/
select year(@datevar) [Year] 

/* Getting the Day Name like monday tuesday... */
SELECT DATENAME(dw, GETDATE())
-- or
/* 0-monday,1-tuesday,2-wednesday ....7-monday,8-tuesday... */
SELECT DATENAME(dw, 0)