SQL SERVER passing getdate() or string date not working correctly

CREATE PROCEDURE sp_ME

@ID int, 
@ThisDate datetime = null

AS
SET NOCOUNT ON


IF @ThisDate IS NULL
BEGIN
    SET开发者_如何学运维 @ThisDate = CURRENT_TIMESTAMP
END

DECLARE @intErrorCode int,  
        @QBegin datetime, 
        @QEnd datetime

SELECT @intErrorCode = @@ERROR


IF @ThisDate BETWEEN '01/01/' + CONVERT(VARCHAR(4), YEAR(@ThisDate))  
             AND '03/31/' + CONVERT(VARCHAR(4), YEAR(@ThisDate))  

BEGIN
    Select @QBegin  = DATEADD(s,0,CAST ('10/01/'  AS varchar(6) ) + 
                      CONVERT(VARCHAR(4),DATEPART (year,@ThisDate)-1))

    Select @QEnd = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@QBegin)+3,0))


    SELECT * FROM   QUERY 
    WHERE       MEID = @ID 
    AND         mydate >= @QBegin 
    AND         mydate <= @QEnd)
END

SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO ErrHandler

ErrHandler:
        RETURN @intErrorCode
GO

It returns a dataset when you leave it blank and it assumes and fills in the date, however when you plug in a date it just states "The command completed successfully."

Any help would be more than appreciated.

At a guess, you need to query the previous quarter's results, which would just be this query:

SELECT * FROM   QUERY 
    WHERE       MEID = @ID 
    AND         mydate >= DATEADD(quarter,DATEDIFF(quarter,'20010101',@ThisDate),'20001001'),
    AND         mydate <  DATEADD(quarter,DATEDIFF(quarter,'20010101',@ThisDate),'20010101'))

And get rid of that big if condition, etc.

You could also get rid of the first if, if you put COALESCE(@ThisDate,CURRENT_TIMESTAMP) in the above, where I currently have @ThisDate.

I use the DATEADD(quarter,DATEDIFF(quarter,'20010101',@ThisDate),'20001001') pattern for a lot of datetime manipulation. It let's you achieve a lot in a few operations. In this case, it's the difference between the two dates ('20010101','20001001') which is giving us the previous quarter.

You'll frequently encounter the DATEADD/DATEDIFF pattern in questions involving removing the time portion from a datetime value. The canonical version of this is DATEADD(day,DATEDIFF(day,0,@Date),0). But the pattern can be generally extended to work with any of the datetime components. If you choose month rather than day, you'll get midnight at the start of the first of the month (of the day you've supplied)

Where it gets tricky is when you use dates (instead of 0), and especially if you don't use the same date for both calculations. This allows you to apply an additional offset that seems almost "free" - you're already using this construct to remove the time component, the fact that you can compute e.g. the last date in a quarter/month/etc is a bonus.