January 16, 2009 at 10:47 am
I need to write function
check asofdate first
then if asofdate is of this month
put date asofdate as it is
but if asofdate is from previous month
then default to end of previous month
please help
January 16, 2009 at 11:18 am
How 'bout you take a stab at it first, then post your code so we can help you? BTW, please include example data and your expected results.
January 16, 2009 at 11:38 am
suppose i have enter asofdate= 1/16/2009
it should be same asofdate =1/16/2009
because this month isn't end
but if i have enter asofdate = 1/16/2008
it should be asofdate=1/31/2008
January 16, 2009 at 11:42 am
OK, now try to write this in T-SQL and post your first stab. We'll take it from there and help you out. Hint...a calendar or dates table would help you out a bunch here.
January 16, 2009 at 12:04 pm
create FUNCTION [dbo].[svf_checkdate]
(@pDate datetime
--parameters
--None
)
RETURNS datetime
AS
BEGIN
-- Return variable
DECLARE @_Asofdate as datetime
--Main Query
select @_Asofdate = c.currentdate from vew_Calendar c where c.CurrentDate= @pDate
select @_Asofdate = c.MonthEndDate from vew_Calendar c where c.MonthEndDate= @pDate
-- Return the result of the function
RETURN @pDate
END
January 16, 2009 at 12:06 pm
i have written t-sql
but how can check entry date is this month or previous month
January 16, 2009 at 1:23 pm
DECLARE @SomeDate datetime,
@Today datetime
SELECT @Today = GETDATE(),
@SomeDate = DATEADD(dd,-30,@Today)
--example with AsofDate 30 days ago
IF MONTH(@SomeDate) = MONTH(@Today) AND YEAR(@SomeDate) = YEAR(@Today)
PRINT 'Same Month'
ELSE PRINT 'Different Month'
SET @SomeDate = DATEADD(dd,-3,@Today)
--example with AsofDate 3 days ago
IF MONTH(@SomeDate) = MONTH(@Today) AND YEAR(@SomeDate) = YEAR(@Today)
PRINT 'Same Month'
ELSE PRINT 'Different Month'
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply