Solved

In T-SQL: How to return "This Month's End Date"

Posted on 2009-03-31
8
693 Views
Last Modified: 2012-05-06
Please see my code snippet.

When running this Function based on today (31/03/2009 which is this months end date) using GETDATE() as follows:
SELECT [dbo].[wfThisMonthEndDate](GETDATE())
I get the 30/03/2009 and I get a time value.

I would like to retrieve 00:00:00 time value, which in tern may sort out the incorrect date, but if it doesn't that needs to be allowed for as well where your running the function on the last day of the month.
I love dynamic things, but sure can be a pain in the head sometimes!
CREATE function [dbo].[wfThisMonthEndDate] (@today as smalldatetime)

Returns smalldatetime AS

BEGIN
 

DECLARE @datestring AS VARCHAR(10)

DECLARE @date SMALLDATETIME; 

SET @datestring = CONVERT(VARCHAR(10),@today,103) 

SET @date = @datestring

DECLARE @start SMALLDATETIME, @endDay SMALLDATETIME; 

SET @start = DATEDIFF(DAY,0,@date); 

SET @endDay = DATEADD(MONTH,1,@date)-DAY(@start); 

SET @datestring = CONVERT(VARCHAR(10),@endday,111) 

RETURN @datestring

END

Open in new window

0
Comment
Question by:Gav-B
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 430 total points
ID: 24027892
CREATE function [dbo].[wfThisMonthEndDate] (@today as smalldatetime)
Returns smalldatetime AS
BEGIN
 
DECLARE @date SMALLDATETIME

SET @date = CONVERT(smalldatetime, CONVERT(varchar, @today, 101))

SET @date = DATEADD(d, -1, DATEADD(m, 1, DATEADD(d, 1 - DATEPART(d, @date))))

RETURN @date
END
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
ID: 24028021
note: usually, you want "next months first's day date", and use < to compare to that date...
0
 

Author Comment

by:Gav-B
ID: 24028379
mattewspatrick:
Your code returns the error: "The dateadd function requires 3 arguments."
angelIII:
Yes the simple solutions are the best :) but there are two reasons why I don't wish to do that in this case:
1) I'm only 6 months into learning SQL, so I don't want to always take the simple option. Sometimes it's nice to learn something new as well as solve your business problem.
+2) The view I'm using is quite large so and has a lot of < & > making it confusing at times so to keep the code down & make it easy to scan read I find it's better to use:
BETWEEN [Func1] AND [Func2] rather than more < & >'s.
Feel free to comment on mattewsparticks code?
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 430 total points
ID: 24028431
Sorry about that.  Try...


CREATE function [dbo].[wfThisMonthEndDate] (@today as smalldatetime)
Returns smalldatetime AS
BEGIN
 
DECLARE @date SMALLDATETIME

SET @date = CONVERT(smalldatetime, CONVERT(varchar, @today, 101))

SET @date = DATEADD(d, -1, DATEADD(m, 1, DATEADD(d, 1 - DATEPART(d, @date), @date)))

RETURN @date
END
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 20 total points
ID: 24028457
Have a doubt:

What's the need for a function to achieve this which you can achieve it in your query itself.
Try the one below:

Replace the getdate() with your date input. If it is not Date input use Cast and make your input string to datetime.
SELECT dateadd(dd,-1,CONVERT(datetime,CONVERT(char(6),dateadd(mm,1,getdate()),112) + '01',112))

Open in new window

0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
ID: 24028643
if there is code that is confusing, make sure you put comments to explain what it does...
that's the best method to make sure that in 1 month or in 10 years, looking at the code, it will still be readable without having heads that ache.
0
 

Author Comment

by:Gav-B
ID: 24028742
rrjegan17:
What's the need...
1) Reuse of solid code & Dynamics
  - Why would I want to type or copy & paste that line lots of times?
  - Using functions you can dynamically update lots of views by changing just the function (i.e. no hard coding).
2) SQL views become a lot easy to read and thus alter quickly with less change of error.
mattewspatrick:
Great stuff, that works! Solution excepted (with some points to angelIII as well).
All I had to change was the 101 to 103 because that's our date setup.

select TOP 500 *
from Points
where [Gav-B] = 'Happy'
-- heh, thanks all ;)
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24028879
Glad to help :)
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

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

706 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

17 Experts available now in Live!

Get 1:1 Help Now