Solved

SQL Date script help

Posted on 2013-11-20
6
323 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:
Scott Pletcher 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

Expert Comment

by:Scott Pletcher
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

773 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