August 1, 2011 at 8:34 am
Hi
Points are high as this is now URGENT.
If I run the following as my function - it works ok, as I think it should
CREATE FUNCTION dbo.fn_StartDate(@Date datetime)
RETURNS varchar(50)
AS
BEGIN
DECLARE @pdddate varchar(50)
DECLARE @calldd int
DECLARE @callmm int
DECLARE @callyyyy int
SET @calldd = day(@date)
SET @callmm = month(@date)
SET @callyyyy = year(@date)
IF @calldd <= 10
SET @PDDDATE = 'Date is 10 or less'
ELSE
IF (@CALLMM = 12)
SET @PDDDATE = 'Date is 10 or more AND Month is 12'
ELSE
SET @PDDDATE = 'Date is 10 or more AND Month is 11 or less'
RETURN (@pdddate)
END
However, as soon as I add additional functionality it wont work.....
CREATE FUNCTION dbo.fn_StartDate(@Date datetime)
RETURNS varchar(50)
AS
BEGIN
DECLARE @pdddate varchar(50)
DECLARE @calldd int
DECLARE @callmm int
DECLARE @callyyyy int
SET @calldd = day(@date)
SET @callmm = month(@date)
SET @callyyyy = year(@date)
IF @calldd <= 10
--SET @PDDDATE = 'Date is 10 or less'
--startdate = 15th of Current Month
select @pdddate = Case When @callmm <10 then '0' end + Cast(@callmm as Varchar) + '15' + Cast(@callyyyy as varchar)
ELSE
/*IF (@CALLMM = 12)
SET @PDDDATE = 'Date is 10 or more AND Month is 12'
ELSE
SET @PDDDATE = 'Date is 10 or more AND Month is 11 or less'*/
--startdate = 15th of Next Month
BEGIN
IF (@CALLMM = 12)
BEGIN
SET @CALLMM = 1
SET @CALLYYYY = @callyyyy +1
select @pdddate = Case When @callmm <10 then '0' end + Cast(@callmm as Varchar) + '15' + Cast(@callyyyy as varchar)
END
ELSE
BEGIN
SET @CALLMM = @CALLMM +1
select @pdddate = Case When @callmm <10 then '0' end + Cast(@callmm as Varchar) + '15' + Cast(@callyyyy as varchar)
END
END
RETURN (@pdddate)
END
Would appreciate some help
Many thanks
August 1, 2011 at 8:45 am
"It won't work" is a little vague. Please post any error messages you get, or show how the results you get differ from those you expect. Table DDL and sample data will probably also come in handy.
Thanks
John
August 1, 2011 at 11:01 am
Both functions are syntactically correct and both return results.
Whether the results are what you're looking for is unknown to us.
I recommend you explain a little more detailed what you're trying to achieve since there might be a much easier way... (if you're trying to reformat a date value)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply