SQL: Play wid dates

1. Get the system date-

select GETDATE() as 'Today''sDate'

Output-
Today'sDate

2014-11-21 00:11:16.593

2. Extract YY, MM, DD, H/M/S from the date
Year/Month/Day
select Year(GETDATE()) as 'Year', Month(GETDATE()) as 'Month', Day(GETDATE()) as 'Day'
Output
Year Month Day
2014 11 21

H/M/S
select DATEPART(HH, getdate()) as 'Hour', DATEPART(MM, getdate()) as 'Minute', DATEPART(S, GETDATE()) as 'Second'

Output
Hour Minute Second

0 11 59

3. DATEPART Function

Syntax

DATEPART(datepart,date)

Commonly Used datepart

datepart	Abbreviation
year	yy, yyyy

month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s

4. DATEADD function
Syntax

DATEADD(datepart,number,date)

e.g. Adding an year to current date
Select DATEADD(YY, 1, getdate()) as 'NextYear'

Output

NextYear

2015-11-21 00:32:33.570

Selecting only date part using Convert function

Select CONVERT(DATE, DATEADD(YY, 1, getdate())) as 'NextYear'
Output
NextYear

2015-11-21

5. DATEDIFF function

Syntax

DATEDIFF(datepart,startdate,enddate)

e.g. Getting days difference between today and yesterday

select DATEDIFF(DD, Getdate()-1, GetDate()) as 'Days between today & yesterday'
Output
Days between today & yesterday

1

6. Sample Problem- 
Find all the employees who were hired in October 2003 from below data

select empid, lastname, firstname, hiredate from HR.employees

empid lastname firstname hiredate
1 Davis Sara 2002-05-01 00:00:00.000
2 Funk Don 2002-08-14 00:00:00.000
3 Lew Judy 2002-04-01 00:00:00.000
4 Peled Yael 2003-05-03 00:00:00.000
5 Buck Sven 2003-10-17 00:00:00.000
6 Suurs Paul 2003-10-17 00:00:00.000
7 King Russell 2004-01-02 00:00:00.000
8 Cameron Maria 2004-03-05 00:00:00.000

select empid, lastname, firstname, hiredate from HR.Employees
 where DATEPART(YY, hiredate)=DATEPART(YY,DATEADD(YY, -11, getdate()))
 AND DATEPART(MM, hiredate)=DATEPART(MM, DATEADD(MM, -1, getdate()))

Output
empid lastname firstname  hiredate
5 Buck Sven 2003-10-17 00:00:00.000

6 Suurs Paul 2003-10-17 00:00:00.000