Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Introduction

In this article I will explain how to use DATEADD function to add or subtract specified time interval from dates in SQL Server.

Description:

In previous articles I explained Substring function Example, SQL Query to remove first and last character from string and many articles relating to SQL Server. Now I will explain how to use DATEADD function to add or subtract specified time interval from dates in SQL Server.

DATEADD function:

This function is used to add or subtract specified time interval from dates in SQL Server. Generally DATEADD function will take 3 arguments.

Declaration of DATEADD function:


DATEADD (datepart, number, date)
In this function

1st Argument "datepart" is the interval type we need to add or subtract for example day, month, year, hour, minute, second.

datepart can be one of the following:

datepart
Abbreviation
year
yy, yyyy
quarter
qq, q
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
microsecond
mcs
nanosecond
ns
2nd Argument the number is the amount of datepart units to add or subtract. For example if datepart was d and the number was 3 then it would add three days to the date specified.

3rd Argument date is the date to add/subtract the number of dateparts from. It can either be a column in the database, a constant value or a function such as GETDATE() which would return the current date and time.

Examples of DATEADD Function:

Ex: 1

Suppose if we want to add one month to present date we need to write the query like this


SELECT GETDATE() AS CurrentDate,DATEADD (mm, 1, GETDATE()) AS NewDate
Output:

CurrentDate
NewDate
2012-10-31 03:10:19.457
2012-11-30 03:10:19.457
Ex: 2

Suppose if we want to subtract one month from present date we need to write the query like this


SELECT GETDATE() AS CurrentDate,DATEADD (mm, -1, GETDATE()) AS NewDate
Output:

CurrentDate
NewDate
2012-10-31 03:14:30.200
2012-09-30 03:14:30.200
Ex: 3

We have Orders table like this

OrderId
OrderName
ShippingDate
1
Aspdotnet-Suresh Book
2012-10-31 03:44:00.053
Now I need to add 15 days to “ShippingDate” for that we need to write the query like this


SELECT OrderId, OrderName, DATEADD(dd,15,ShippingDate) As ShippingDate FROM Orders
Output:

OrderId
OrderName
ShippingDate
1
Aspdotnet-Suresh Book
2012-11-15 03:44:00.053
This way we can use DATEADD function to add or subtract dates from specified time interval in SQL Server.

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

2 comments :

vipin pandey said...

hi suresh ji! you r doing great job.thanks for you articles.

sir,its my request to u ,please teach us MVC and WCF.
highly thankful to you
Vipin Kumar Pandey

Abhinavsingh993 said...

Sir,it is great Sir can you please guide me the technique regarding "Password Encryption with Salt values" so that even a hacker cannot able to decrypt it with the help of any decryption Algorithm
As always I told your work will oneday proved to be as a "Mother of ASP.NET learners"

Give your Valuable Comments

Other Related Posts

© 2010-2012 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.