Click here to Skip to main content
15,888,610 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

SQL
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.

SQL
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 Start time to Midnight (at the befining of the day)
	set @StartTime = CONVERT(DateTime, DATEDIFF(Day, 0, @P1))
	-- Set Finish time to Noon
	Set @FinishTime = DATEADD(hh,12,@StartTime)
	
End

if(@P2 = 'PM')
Begin
	-- Set start time to Midnight (at the begining of the day)
	set @StartTime = CONVERT(DateTime, DATEDIFF(Day, 0, @P1))
	-- Set start time to Noon
	set @StartTime = DATEADD(HH,12,@StartTime)
	-- Set Finishtime to Midnight (at the end of the day)	
	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.
Posted
Updated 9-Jun-16 19:47pm
v3

1 solution

You're doing it the hard way and the way that is the worst for scalability. You're using DATEPART in the WHERE clause, which will cause each and every record to be parsed and compared to the conditions. Don't do this.

Setup two datetimes based on your AM/PM and the date you want. Notice that your P! parameter contains a date AND a time? Use this to your advantage. Create two datetime values from the date you get and the add the time part based on the AM or PM parameter.

For an AM on 2016-06-09, your datetime range would end up being '2016-06-09 00:00:00.000' to '2016-06-09 12:00:00.000'. For a PM it's going to be nearly the same, '2016-06-09 12:00:00.000' to '2016-06-10 00:00:00.000'. Notice that the end time is midnight the next day. This avoids having to do a 11:59:59.999, which may not be entirely accurate.

Your WHERE clause is now much more performant since it doesn't have to do any function calls for every record. It's just a straight datetime value compare between two datetime values.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900