Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

t-sql add days to getdate function

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
maqskywalker
Asked:
maqskywalker
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
PortletPaulCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now