The list of styles that can be used for Datetime are:

Style ID Style Type
0 or 100 mon dd yyyy hh:miAM (or PM)
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
13 or 113 dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
20 or 120 yyyy-mm-dd hh:mi:ss(24h)
21 or 121 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mm/yy hh:mi:ss:mmmAM
=======================================================
Samples Below :
========================================================
select convert(varchar(20),getdate(),100)--Jan 17 2009 12:28PM
select convert(varchar(20),getdate(),101)--01/17/2009
select convert(varchar(20),getdate(),102)--2009.01.17
select convert(varchar(20),getdate(),103)--17/01/2009
select convert(varchar(20),getdate(),104)--17.01.2009
select convert(varchar(20),getdate(),105)--17-01-2009
select convert(varchar(20),getdate(),106)--17 Jan 2009
select convert(varchar(20),getdate(),107)--Jan 17, 2009
select convert(varchar(20),getdate(),108)--12:28:15
select convert(varchar(20),getdate(),109)--Jan 17 2009 12:28:15
select convert(varchar(20),getdate(),110)--01-17-2009
select convert(varchar(20),getdate(),111)--2009/01/17
select convert(varchar(20),getdate(),112)--20090117
select convert(varchar(20),getdate(),113)--17 Jan 2009 12:28:15
select convert(varchar(20),getdate(),114)--12:28:15:087
select convert(varchar(20),getdate(),13)---17 Jan 2009 12:28:15

================================================================
use pubs

go

---------------- Example for the demonstration of use of style while input of data--------------
/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn datetime
)
go


/* Inserting the test values into the table */
-- Inserting in US format
insert into MyDateTest99 select convert(datetime,'05/08/2004',101)
-- Inserting in UK format
insert into MyDateTest99 select convert(datetime,'08/05/2004',103)
-- Inserting in ISO Format
insert into MyDateTest99 select convert(datetime,'20040508',112)
go

/* Selecting the result */
select DateColumn from MyDateTest99
go

/* Performing Cleanup */
drop table MyDateTest99
go

use pubs

go

---------------- Example for the demonstration of use of style while output of data--------------

/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn datetime
)
go
/* Inserting the test values into the table */
insert into MyDateTest99 select convert(datetime,'05/08/2004',101)

go

/* Selecting the result */
-- In US Format
select convert(varchar,DateColumn,101) from MyDateTest99
-- In UK Format
select convert(varchar,DateColumn,103) from MyDateTest99
-- In ISO Format
select convert(varchar,DateColumn,112) from MyDateTest99

go
/* Performing Cleanup */
drop table MyDateTest99
go
=====================================================================
Some other functions that can be used for various purposes are DATEADD, DATEDIFF, DATENAME, DATEPART, DAY, GETDATE, MONTH, and YEAR. Here's some further detail on these functions as well as a code sample showing their use:

Dateadd: Returns a new datetime value based on adding an interval to the specified date.

Syntax: DATEADD ( datepart, number, date )

Datediff: Returns the number of date and time boundaries crossed between two specified dates.

Syntax: DATEDIFF ( datepart, startdate, enddate )

Datename: Returns a character string representing the specified datepart of the specified date.

Syntax: DATENAME ( datepart, date )

Datepart: Returns an integer representing the specified datepart of the specified date.

Syntax: DATEPART ( datepart, date )

Day: Returns an integer representing the day datepart of the specified date.

Syntax: DAY ( date )

Getdate: Returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values.

Syntax: GETDATE ( )

Month: Returns an integer that represents the month part of a specified date.

Syntax: MONTH ( date )

Year: Returns an integer that represents the year part of a specified date.

Syntax: YEAR ( date )

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]