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 :)

sql server DATENAME () Function

DATENAME () function can use for date and time related operation in Sql server called DATENAME (). The DATENAME () returns a character string representing the specified datepart of the specified.date.

DATENAME() Syntax

DATENAME(datepart,datetime)

Datepart: Is the parameter that specifies the part of the date to return.

Datetime: Is an expression that returns a datetime or smalldatetime value.

Examples

DECLARE @Today DATETIME

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

SELECT DATENAME(month, @Today) -- Get the month from date

SELECT DATENAME(mm, @Today )

SELECT DATENAME(m, @Today)

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

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

--December

SELECT DATENAME(dayofyear, @Today)  -- select day of year, its return current day from 1 of jan 

SELECT DATENAME(dy,@Today)

SELECT DATENAME(y, @Today)

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

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

--349

SELECT DATENAME(day, @Today) -- select the day of the month

SELECT DATENAME(dd, @Today)

SELECT DATENAME(d, @Today)

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

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

--15

SELECT DATENAME(week, @Today) -- get week numbre of the year

SELECT DATENAME(wk,@Today)

SELECT DATENAME(ww, @Today)

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

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

--51

SELECT DATENAME(hour, @Today) -- select the current hour for now

SELECT DATENAME(hh, @Today)

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

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

--13

SELECT DATENAME(minute, @Today) -- select the current minute for now

SELECT DATENAME(mi, @Today)

SELECT DATENAME(n, @Today)

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

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

--45

SELECT DATENAME(second , @today) -- select the current seocnd for now

SELECT DATENAME(ss, @today)

SELECT DATENAME(s, @today)

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

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

--34

SELECT DATENAME(millisecond , @today) -- select the current milliseconds for now

SELECT DATENAME(ms, @today)

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

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

--340

I got this stuff from codegain.com, by understanding its importance I’m republishing it.
Have a nice day... 'N happy Coding :)

Download myshedular 0.0.1.2 (Beta)

Hi frienz,

myshedular is back with a few update allowing user to select exe file dynamically [My main Goal]. As some users told not working in guest profile, i fixed that also in updated version.

 Demo

But some more are hidden have pump it out. As usual documentation and code and exe are on this link. Check it out.

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

Partially updating a webpage without refresh, using ajax Timer

Hi frienz,
wishing u all a merry Xmas. last day i was trying to hijack the countdown of rapid-share. My bad i think they where having some piece of script their but i didn't find anything :(. Then i done some googling and got some piece  that they are using some server side code. I hope they are using ajax without post-back they need it, i hope so
Demo

On aspx file

<form id="form1" runat="server">

    <div>

        <asp:ScriptManager ID="ScriptManager1" runat="server">

        </asp:ScriptManager>

        <div>

            <asp:UpdatePanel ID="UpdatePanel1" runat="server">

                <Triggers>

                    <asp:AsyncPostBackTrigger ControlID="Timer1" EventName="Tick" />

                </Triggers>

                <ContentTemplate>

                    <asp:Timer ID="Timer1" runat="server" Interval="1000" OnTick="Timer1_Tick">

                    </asp:Timer>

                    <center>

                        <h1>

                            <asp:Label ID="lblTimer" ForeColor="Brown" runat="server"></asp:Label>

                        </h1>

                    </center>

                </ContentTemplate>

            </asp:UpdatePanel>

        </div>

        <table><tr><td>Simply asp.net</td></tr></table>

    </div>

    </form>

on Aspx.cs file

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

        }

    }

    protected void Timer1_Tick(object sender, EventArgs e)

    {

        lblTimer.Text = DateTime.Now.Hour.ToString() + " Hours " + DateTime.Now.Minute.ToString() + " Minutes " + DateTime.Now.Second.ToString() + " Seconds " + DateTime.Now.Millisecond.ToString()+" Milliseconds ";

    }

hope u Got what u looking

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