SQL Date script help

I'm needing to get the 15th of last month.  The script I currently have gets me the 15th for current month.

select cast(convert(varchar(10), dateadd(dd,datediff(dd,datepart(dd,getdate()),15),getdate()), 101) as datetime)
LVL 1
abarefootAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
SELECT DATEADD(DAY, 14, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0))

--Remove the "- 1" to get the 15th of the current month
0
 
abarefootAuthor Commented:
Thanks for the help.  That's what I needed.
0
 
abarefootAuthor Commented:
one other question.  How would I get this to show time as 23:59 and not 00:00.  In other words at the end of day not beginning.
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Scott PletcherSenior DBACommented:
For the 15th, and as 23:59[:00.000], then like this:

SELECT DATEADD(MINUTE, -1, DATEADD(DAY, 15, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)))

If you want 23:59:59.997 (for a datetime rather than a smalldatetime), change "MINUTE, -1" to "MILLISECOND, -3".
0
 
abarefootAuthor Commented:
Thanks
0
 
PaulCommented:
>>"How would I get this to show time as 23:59"
mmmm, this indicates you are using "between" and that can be a problem

if you are using the 15th of a Month calculation for selecting dates ranges

e.g.

xyz between '2013-10-15' and '2013-11-14 23:59:59.997'

Then the WAY easier and more accurate method is to stop using between, e.g.

( xyz >= '2013-10-15' and xyz < '2013-11-15' )

for more on this: "Beware of Between"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.