codetoad.com
  ASP Shopping CartForum & BBS
  - all for $20 from CodeToad Plus!
  
  Home || ASP | ASP.Net | C++/C# | DHTML | HTML | Java | Javascript | Perl | VB | XML || CodeToad Plus! || Forums || RAM 
Search Site:



Home » ASP » Article

Add or Subtract Hours in SQL or ASP using DateAdd

Article by:  Jeff Anderson  ( 1362 ) (3/17/2003)
Bookmark us now! Add to Favourites
Email a friend!Tell a friend
Summary: A beginners guide to using the SQL DATEADD function to add or subtract hours. Particularly useful when setting the time displayed on the ASP page to a different time zone (eg when the server is in the US, and the site is for a UK audience).
Viewed: 497034 times Rating (51 votes): 
 3.9 out of 5
 Rate this Article  Read Comments  Post Comments

Add or Subtract Hours in SQL or ASP using DateAdd



I had a situation with some code where I needed to display the time of a post, but the site was a UK based community. Trouble was, my server was in the US. So I needed on a regular basis to add 5 hours to the date any one made a post.

It's actually very simple to do, using the DateAdd function. Here it is for SQL server

Select All Code


and for Access:

Select All Code


You can use this for both ASP pages and for SQL code in SQL server.

Like the similar DatePart function we specify in the first part the type of time (hours, days, minutes etc) and then the number to add, then the date (in this case the current date, but it might be a selected date variable). So the above code adds 5 hours to the current time - perfect!

If you need to subtract, you still use DateAdd but use minus numbers.

Here's the full list of options

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week of year
h Hour
n Minute
s Second


One interesting point with adding months. Let's say you have a date 31st of Jan and you add a month. It will not just say 31st Feb (which doesn't exist of course), but will pick the last day of the month, even selecting 29th of Feb if it happens to be a leap year!




CodeToad Experts

Can't find the answer?
Our Site experts are answering questions for free in the CodeToad forums
Rate this article:     Poor Excellent
View highlighted Comments
User Comments on 'Add or Subtract Hours in SQL or ASP using DateAdd'
RELATED ARTICLES
ASP FilesystemObject
by Jeff Anderson
An introduction to the Filesystemobject
ASP GetTempName
by Jeff Anderson
Use the GetTempName method to create a randomly generated temporary file on the server.
ASP Format Date and Time Script
by Jeff Anderson
An ASP script showing the variety of date and time formats possible using the FormatDateTime Function.
ASP OpenTextFile
by Jeff Anderson
An introduction to the OpenTextFile Method of the FileSystemObject
Email validation using Regular Expression
by Jeff Anderson
Using regular expression syntax is an exellent way to thoroughly validate an email. It's possible in ASP.
Add or Subtract Hours in SQL or ASP using DateAdd
by Jeff Anderson
A beginners guide to using the SQL DATEADD function to add or subtract hours. Particularly useful when setting the time displayed on the ASP page to a different time zone (eg when the server is in the US, and the site is for a UK audience).
The asp:radiobutton and asp:radiobuttonlist control
by David Sussman, et al
In HTML, radio buttons are used when we need to make multiple sets of choices available, but we want the user to select only one of them.
The asp:checkbox and asp:checkboxlist control
by David Sussman, et al
Checkboxes are similar to radio buttons, and in HTML, they were used to allow multiple choices from a group of buttons.
ASP FileExists
by Jeff Anderson
An introduction to the FileExistsMethod of the FileSystemObject
Concatenate strings in sql
by Jeff Anderson
A brief introduction to concatenating strings in an sql query (using SQL server or access databases).








Recent Forum Threads
• Run a program both on windows and linux
• VERO.SurfCAM.v2014
• Schlumberger.Petrel.V2013.2
• Petrel.V2013.2
• Altair.HyperWorks.v12
• VoluMill.v6.1
• VoluMill.NEXION.6
• VERO.SurfCAM.v2014
• Schlumberger.Petrel.V2013.2


Recent Articles
ASP GetTempName
Decode and Encode UTF-8
ASP GetFile
ASP FolderExists
ASP FileExists
ASP OpenTextFile
ASP FilesystemObject
ASP CreateFolder
ASP CreateTextFile
Javascript Get Selected Text


© Copyright codetoad.com 2001-2014