Solved

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

Posted on 2009-03-31
8
700 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 143

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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
 
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 143

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 our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Learning SQL Reporting Services 2014 1 40
Need help constructing a conditional update query 16 71
table joins in qry 17 63
Setting variables in a stored procedure 5 21
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

830 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