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!!

Christmas 2009,

Merry Christmas and happy new year to all!

may all your dreams and wishes come true!

New year resolution, sticking to target of 14 post per week.