Solved

SQL Date script help

Posted on 2013-11-20
6
319 Views
Last Modified: 2013-11-20
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)
0
Comment
Question by:abarefoot
  • 3
  • 2
6 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39663429
SELECT DATEADD(DAY, 14, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0))

--Remove the "- 1" to get the 15th of the current month
0
 
LVL 1

Author Closing Comment

by:abarefoot
ID: 39663452
Thanks for the help.  That's what I needed.
0
 
LVL 1

Author Comment

by:abarefoot
ID: 39663576
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39663834
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
 
LVL 1

Author Comment

by:abarefoot
ID: 39663863
Thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39665076
>>"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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

758 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

18 Experts available now in Live!

Get 1:1 Help Now