T-SQL daily usage

first of 20 odd posts I am hoping to post over next few days on T-SQL

Date and time functions...

this post contains some basic but usefull date functions I use almost daily basis,


1. Complete date plus hours, minutes, seconds and a decimal fraction of a
second

select convert(varchar(32), getdate(), 126)

result : 2009-12-29T15:50:44.883

2. Get the current date time

select Getdate()

result : 2009-12-29 15:57:17.290

3. Converting integer in date format,

Declare @RunDate datetime
Set @RunDate=convert(varchar(8), 20091229)

select @RunDate
result : 2009-12-29 00:00:00.000

4. Add and Substract dates

Months

Declare @startDate datetime
Set @startDate=DATEADD(m, -15, Getdate())

select @startDate

result : 2008-09-29 16:13:49.687

Years

Declare @startDate datetime
Set @startDate=DATEADD(yyyy, -2, Getdate())

select @startDate

result : 2007-12-29 16:23:26.663

Days

Declare @startDate datetime
Set @startDate=DATEADD(dd, -10, Getdate())

select @startDate

result : 2009-12-19 16:25:30.963

adding days ,

Declare @startDate datetime
Set @startDate=DATEADD(dd, +10, Getdate())

select @startDate
result : 2010-01-08 16:26:41.093

Happy New year!!