Welcome 2011

With a pray we can welcome 2011 

. Oh god, pls give us the strength and health to overcome all the troubles and Pls keep the world around me in peace.

i Wishing  wish u all Fantastic,wonderful, amazing new year

Have a nice day... 'N happy Coding :)

DATEADD function in Sql Server

Introduction

The DATEADD function is a powerful built-in function sql server to add dates in different way and types. So I would like to explore in this tip how we can use the DATEADD function for different purpose.

 DATEADD Function Syntax

DATEADD(datepart,number,date)

DatePart: Is the parameter that specifies on which part of the date to return a new value

Number: Is the value used to increment datepart

Date: Is an expression that returns a datetime or smalldatetime value

Supportable abbreviations for Datepart  

DatePart	Abbreviations
Year	yy,yyyy
Quarter	q,qq
Month	mm,m
DayOfyear	dy,y
Day	dd,d
Week	wk,ww
WeekDay	dw,w
Hour	hh
Minute	mi,n
Second	ss,s
MilliSecond	ms

 Examples:

DECLARE @Today DATETIME

SET @Today=GETDATE() --'13-Dec-2010'

SELECT DATEADD(Year, 5, @Today) AS dest_date  -- add 5 years from today date

------------Output-----------------------------

----------------------------------------------

--2015-12-13 11:53:21.707

SELECT DATEADD(quarter, 2, @Today) AS NewDate -- add 2 quarter with today date

------------Output-----------------------------

----------------------------------------------

--2011-06-13 11:55:38.033

SELECT DATEADD(Month, 3, @Today) AS NewDate -- add 4 months with current date 

------------Output-----------------------------

----------------------------------------------

--2011-03-13 11:57:23.267

SELECT DATEADD(dayofyear,10, @Today) AS NewDate -- add 10 days with day of year(current day)

------------Output-----------------------------

----------------------------------------------

--2010-12-23 11:57:56.453

SELECT DATEADD(Day, 5, @Today) AS NewDate -- add 5 days with today

------------Output-----------------------------

----------------------------------------------

--2010-12-18 11:57:56.453

SELECT DATEADD(Week, 2, @Today) AS NewDate -- add two weeks with today date

------------Output-----------------------------

----------------------------------------------

--2010-12-27 12:00:14.173

SELECT DATEADD(Weekday, 2, @Today) AS NewDate -- add two weekdays with today date

------------Output-----------------------------

----------------------------------------------

--2010-12-27 12:00:14.173

SELECT DATEADD(Hour, 7, @Today) AS NewDate -- add 7 hours with now date and time 

------------Output-----------------------------

----------------------------------------------

--2010-12-13 15:01:19.313

SELECT DATEADD(minute, 6, @Today) AS NewDate -- add 6 minutes with current time

------------Output-----------------------------

----------------------------------------------

--2010-12-13 12:08:31.517

SELECT DATEADD(minute, 6.5, @Today) AS NewDate -- add 6.5 minutes with current time

------------Output-----------------------------

----------------------------------------------

--2010-12-13 12:08:31.517

-- Note : See i have aded 6 and 6.5 minutes with datepart minutes, but result is same. beucase sql server 6.5 will be 6

SELECT DATEADD(second, 15, @Today) AS NewDate -- add  15 seconds with curren time

------------Output-----------------------------

----------------------------------------------

--2010-12-13 12:33:31.517

SELECT DATEADD(millisecond, 300, @Today) AS NewDate -- add 300 milliseconds to current time

------------Output-----------------------------

----------------------------------------------

--2010-12-13 12:06:15.893

Have a nice day... 'N happy Coding :)

DATEDIFF() function in Sql Server

Introduction

This is the third tips date related function in sql server. In this I going to show you how to use DATEDIFF() function in sql server. The DATEDIFF () is useful to get the specified date part between two dates

DATEDIFF() Syntax

DATEDIFF(datepart,Startdate,EndDate)

datepart: Is the parameter that specifies on which part of the date to return a new value

startdate: Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable or string literal. startdate is subtracted from enddate.

enddate: same as like startdate.

 List of support abbreviation for datepart

DatePart	Abbreviations
Year	yy,yyyy
Quarter	q,qq
Month	mm,m
DayOfyear	dy,y
Day	dd,d
Week	wk,ww
WeekDay	dw,w
Hour	hh
Minute	mi,n
Second	ss,s
MilliSecond	ms

Examples

DECLARE @startdate datetime

declare @enddate datetime

set @startdate=DATEADD(MONTH,-1,getdate());

set @enddate=GETDATE();

SELECT DATEDIFF(Year, @startdate, @enddate) AS DiffYears -- get no of years different between two dates

------------Output-----------------------------

----------------------------------------------

-- 0 Years

SELECT DATEDIFF(QUARTER, @startdate, @enddate) AS DiffYears -- get no of quarters different between two dates

------------Output-----------------------------

----------------------------------------------

-- 0 quarters

SELECT DATEDIFF(MOnth, @startdate, @enddate) AS DiffYears -- get no of months different between two dates

------------Output-----------------------------

----------------------------------------------

-- 1 Months

SELECT DATEDIFF(week, @startdate, @enddate) AS DiffYears -- get no of weeks different between two dates

------------Output-----------------------------

----------------------------------------------

-- 4 weeks

SELECT DATEDIFF(day, @startdate, @enddate) AS DiffYears -- get no of days different between two dates

------------Output-----------------------------

----------------------------------------------

-- 30 days

SELECT DATEDIFF(hour, @startdate, @enddate) AS DiffYears -- get no of hours different between two dates

------------Output-----------------------------

----------------------------------------------

-- 720 hours

SELECT DATEDIFF(MINUTE, @startdate, @enddate) AS DiffYears -- get no of minutes different between two dates

------------Output-----------------------------

----------------------------------------------

-- 43200 minutes

SELECT DATEDIFF(SECOND, @startdate, @enddate) AS DiffYears -- get no of seconds different between two dates

------------Output-----------------------------

----------------------------------------------

-- 2592000 seconds

Have a nice day... 'N happy Coding :)