November 18, 2008 at 8:23 am
Hi all, due to a lack of immediate functions for processing dates in SQL we created two custom functions called start of month (SOM) and end of month (EOM) which takes 2 user inputs, the first being the date that you wish to use the function on to return the start of month (I.E. 18/11/2008 we want 01/11/2008 returning) and the second parameter is to specify how many months you wish to go back so (INPUT: funcSOM(GETDATE(),0) RETURNS 01/11/2008 00:00:00 and INPUT: funcEOM(GETDATE(),0) RETURNS 30/11/2008 00:00:00)
Function Code posted below;
--============================================================================
CREATE FUNCTION [dbo].[funcSOM]
(
@myDate DATETIME,
@myMonthsAgo INT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @myInterimDate DATETIME
SELECT @myInterimDate = DATEADD(m,(-@myMonthsAgo),@myDate-(DATEPART(d,@myDate))) -- START OF MONTH, x MONTHS AGO
SELECT @myInterimDate = CONVERT(DATETIME,CONVERT(INT,@myInterimDate)) -- REMOVE TIMEPART
RETURN @myInterimDate -- RETURN
END
--============================================================================
CREATE FUNCTION [dbo].[funcEOM]
(
@myDate DATETIME,
@myMonthsAgo INT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @myInterimDate DATETIME
SELECT @myInterimDate = DATEADD(m,(-@myMonthsAgo+1),@myDate-(datepart(d,@myDate))) -- START OF MONTH, x-1 MONTHS AGO
SELECT @myInterimDate = @myInterimDate-1 -- TAKE AWAY ONE DAY TO GET LAST DAY OF PREVIOUS MONTH
SELECT @myInterimDate = CONVERT(DATETIME,CONVERT(int,@myInterimDate)) -- REMOVE TIMEPART
RETURN @myInterimDate -- RETURN
END
--============================================================================
Okay, so here is the issue...
The functions work when we use GETDATE() as the first variable. But when we pass a value from a table we always get the last day of the month prior
For Example:
INPUT: funcSOM(GETDATE(),0) RETURNS 01/11/2008 00:00:00 --This is working fine...
INPUT: SELECT dbo.funcSOM(Date,0) FROM dbo.Table_1 RETURNS 31/10/2008 00:00:00 --ERROR??
The data type use in the error examples are all as 'datetime' data types...
What am I missing? Please any help appreciated!
November 18, 2008 at 10:11 am
Okay I the solution and think I know why (to a degree), the issue is about how the functions handle times... In short using another function I have called funcDateOnly, I strip the time away from the date being used in the two functions within the functions themselves and a minor modification to the addition of days...
Code posted below;
--==============================================================================
--funcDateOnly
CREATE FUNCTION [dbo].[funcDateOnly](@DateTime DateTime)
-- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
RETURNS DATETIME
AS
BEGIN
RETURN dateadd(dd,0, datediff(dd,0,@DateTime))
END
--=============================================================================
--funcSOM
CREATE FUNCTION [dbo].[funcSOM]
(
@myDate DATETIME,
@myMonthsAgo INT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @myInterimDate DATETIME
SELECT @myInterimDate = DATEADD(m,(-@myMonthsAgo),(dbo.funcDateOnly(@myDate))-(DATEPART(d,(dbo.funcDateOnly(@myDate))))+1) -- START OF MONTH, x MONTHS AGO
--SELECT @myInterimDate = CONVERT(DATETIME,CONVERT(INT,@myInterimDate)) -- REMOVE TIMEPART
RETURN @myInterimDate -- RETURN
END
--=============================================================================
--funcEOM
CREATE FUNCTION [dbo].[funcEOM]
(
@myDate DATETIME,
@myMonthsAgo INT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @myInterimDate DATETIME
SELECT @myInterimDate = DATEADD(m,(-@myMonthsAgo+1),(dbo.funcDateOnly(@myDate))-(datepart(d,(dbo.funcDateOnly(@myDate))))+1) -- START OF MONTH, x-1 MONTHS AGO
SELECT @myInterimDate = @myInterimDate-1 -- TAKE AWAY ONE DAY TO GET LAST DAY OF PREVIOUS MONTH
RETURN @myInterimDate -- RETURN
END
--=============================================================================
November 18, 2008 at 11:15 am
Quick question, how is the second value used? Does it always go backwards or can it go forward.
Example:
declare @TestDate datetime;
set @TestDate = getdate(); -- 2008-11-18 11:15:35.333
select SOM(@TestDate, 0); -- returns 2008-11-01
select SOM(@TestDate, 1); -- returns 2008-10-01 or 2008-12-01
select SOM(@TestDate, -1); -- returns 2008-10-01 or 2008-12-01
This will help me figure out what you need.
November 18, 2008 at 11:23 am
Here is some test code for you to check out:
declare @TestDate datetime,
@Interval smallint;
set @Interval = 0;
set @TestDate = getdate();
select dateadd(mm, datediff(mm, 0, @TestDate) - @Interval, 0); -- Start of Month
select dateadd(mm, datediff(mm, 0, @TestDate) + 1 - @Interval, -1); -- End of Month
set @Interval = 1;
select dateadd(mm, datediff(mm, 0, @TestDate) - @Interval, 0); -- Start of Month
select dateadd(mm, datediff(mm, 0, @TestDate) + 1 - @Interval, -1); -- End of Month
November 19, 2008 at 2:27 am
The second value will always go backward as all our reporting will be historical although if we can go forward as in your example that would be great, but the functions I have written were only designed to go back.
November 19, 2008 at 7:04 am
The code samples I provided will actually go either way. They go backward with positive values, and forward with negative values. A little counter-intuitive, but that was because I wasn't writing them for the purpose of going both ways. I based them off the assumption you only wanted to go historical.
To make them intuitive, just change the "- @Interval" to "+ @Interval".
November 20, 2008 at 12:09 pm
You seem to be doing a lot of unnecessary calculations and conversions. Try these:
CREATE FUNCTION dbo.SOM(
@myDate DATETIME,
@myMonthsAgo INT
)
RETURNS DATETIME
AS BEGIN
DECLARE @Result DATETIME;
SELECT @Result = DATEADD(m, DateDiff( mm, 0, @MyDate ) - @myMonthsAgo, 0 );
RETURN @Result;
END
go
CREATE FUNCTION dbo.EOM(
@myDate DATETIME,
@myMonthsAgo INT
)
RETURNS DATETIME
AS BEGIN
DECLARE @Result DATETIME;
SELECT @Result = DateAdd( m, DateDiff( mm, 0, @MyDate ) - @myMonthsAgo + 1, 0 );
RETURN @Result - 1;
END
go
declare @TestTable table(
TestDate datetime,
MonthPast smallint
);
insert @TestTable( TestDate, MonthPast )
select GetDate(), 0 union all
select GetDate(), 1 union all
select GetDate(), 10 union all
select '2008-07-15', 0 union all
select '2008-07-15', 1 union all
select '2008-07-15', 10;
select TestDate, MonthPast,
dbo.SOM( TestDate, MonthPast ) as MonthStart,
dbo.EOM( TestDate, MonthPast ) as MonthEnd
from @TestTable;
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply