January 13, 2009 at 4:30 pm
This is part of a stored procedure. Based on the input, I need to query greater than a certain date.
I cannot seem to set the @myDate variable based on the input: This is what I have so far.
DECLARE @monthInput int
SET @monthInput = 1
DECLARE @curDate DATETIME
DECLARE @myDate DATETIME
SET @curDate = getdate()
CASE
WHEN @monthInput=1 THEN @myDate = SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@curDate))-1),DATEADD(mm,1,@curDate)),101)
WHEN @monthInput=2 THEN @myDate = SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@curDate))-1),DATEADD(mm,2,@curDate)),101)
Else @myDate = @curDate
END
Thank you,
Norbert
January 13, 2009 at 5:08 pm
In SQL, CASE is a function and not a statement:
Select @myDate = CASE
WHEN @monthInput=1 THEN SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@curDate))-1),DATEADD(mm,1,@curDate)),101)
WHEN @monthInput=2 THEN SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@curDate))-1),DATEADD(mm,2,@curDate)),101)
Else @curDate
END
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 13, 2009 at 5:37 pm
RBarryYoung,
Thank you. Your assistance is very helpful and solved my issue!
Norbert
January 13, 2009 at 6:31 pm
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply