Hi There,
I have hunted around but can't quite find what I'm doing wrong.
Problem:
What I am trying to do is return records for one particular day then further reduce the results to AM or PM.
I get a "Incorrect Syntax error Near '<'"
Any Ideas what I'm doing wrong in my case statement or a better way to do the same thing?
I just want to pass in the selection criteria as parameters when this is run as a stored procedure. The date selection bit works OK just not the AM PM bit.
I'm Using SQL 2008 R2
Thanks in advance
David.
Declare @P1 datetime
Declare @P2 varchar(2)
set @P1 = '2016-06-09 00:00:00.000'
Set @P2 = 'AM'
SELECT
PP4_MAILBACKUP_ID,
RFC822_txt,
SENDER_smtp,
[SUBJECT],
TOa,
ZIPCOMPRESSION
FROM Senders
Where CAST(Date_Sent as DATE) = CAST (@P1 as DATE)and (ZIPCOMPRESSION = 0)
AND
CASE
When @P2='AM' Then (DATEPART(hh,Date_Sent) < 12)
When @P2='PM' Then (DATEPART(hh,Date_sent) >= 12)
END
Answer:
Thanks to Dave Kreskowiak's answer for putting me on the right track.
I certainly was going about it the hard way.
I came up with the following code that seems to work.
Thanks for not giving me the answer as SQL and making me actually think about it :)
Still only P1 and P2 will be passed in to the stored procedure.
The others will be local variables.
BTW, P1 Could be any time of the day not always 00:00:00.000 that was just my example time.
Declare @P1 datetime
Declare @P2 varchar(2)
Declare @StartTime datetime
Declare @FinishTime datetime
Declare @SearchDate date
set @P1 = '2016-06-09 00:00:00.000'
Set @P2 = 'AM'
Set @SearchDate = Cast(@P1 as DATE)
if(@P2 = 'AM')
Begin
set @StartTime = CONVERT(DateTime, DATEDIFF(Day, 0, @P1))
Set @FinishTime = DATEADD(hh,12,@StartTime)
End
if(@P2 = 'PM')
Begin
set @StartTime = CONVERT(DateTime, DATEDIFF(Day, 0, @P1))
set @StartTime = DATEADD(HH,12,@StartTime)
Set @FinishTime = DATEADD(HH,12,@StartTime)
End
Print @StartTime
Print @FinishTime
SELECT
PP4_MAILBACKUP_ID,
DATE_sent,
RFC822_txt,
SENDER_smtp,
[SUBJECT],
TOa,
ZIPCOMPRESSION
FROM Senders
Where CAST(Date_Sent as DATE) = @SearchDate
And (ZIPCOMPRESSION = 0)
And DATE_sent >= @StartTime
And DATE_sent < @FinishTime
Order by DATE_sent
What I have tried:
Various different formats for the Case Statement.