Solved

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

Posted on 2009-03-31
8
696 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL join help to a thrid table 51 76
How to base a filter depending on fields contents? 15 47
Help Extract Specific in SQL 8 21
Extract string portion 2 12
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…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

912 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

20 Experts available now in Live!

Get 1:1 Help Now