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
and for Access:
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!
|
Useful Links
|
|
