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

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

Gav-BAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
note: usually, you want "next months first's day date", and use < to compare to that date...
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Gav-BAuthor Commented:
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
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
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
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
Gav-BAuthor Commented:
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
 
Patrick MatthewsCommented:
Glad to help :)
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.