Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-03-31
8
Medium Priority
?
710 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 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1720 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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 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
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!

 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1720 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
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 80 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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 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 93

Expert Comment

by:Patrick Matthews
ID: 24028879
Glad to help :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Integration Management Part 2
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

971 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