Solved

t-sql add days to getdate function

Posted on 2014-12-11
2
248 Views
Last Modified: 2014-12-11
When I run this:

-- end of todays date
SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '23:59:59')

I get this:

2014-12-11 23:59:59.000

How do I add 5 days to this.
I want 5 days added to GETDATE()  in the same format .

So I want to get this:

2014-12-16 23:59:59.000
0
Comment
Question by:maqskywalker
2 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
Comment Utility
Since the code already uses DATEADD, just add five to the middle parameter (increment)

SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) + 5, '23:59:59')
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
Comment Utility
DO NOT USE '23:59:59' because that is NOT the end of the day.

It is one second short of the end of the day.

The end of today's day is ( 00:00:00 of tomorrow )
e.g.
if today is 16th December 2014, then the end of that day is '2014-12-17 00:00:00'

to set time to 00:00:00 from getdate() use
dateadd(day, datediff(day,0, getdate() ), 0)
or
cast(getdate() as date)

then to add 5 days use dateadd(day,5, .... ) where ... is one of the options above

also note to avoid use of between for date/time ranges.
see: "Beware of Between"
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now