Sql选择特定日期的记录，然后选择AM或PM [英] Sql select records for a particular date then AM or PM

问题描述

嗨那里，

我已经找到了但却找不到我做错了什么。



问题：

我要做的是返回特定日期的记录，然后将结果进一步减少到AM或PM。



我收到不正确的语法错误附近'<'



任何想法我的意思我在我的案例陈述中做错了或者做出同样事情的更好方法吗？



我只想将选择标准作为参数传递给它作为一个存储过程。日期选择位工作正常而不是AM PM位。



我正在使用SQL 2008 R2



提前致谢

David。

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

答案：

感谢Dave Kreskowiak的回答，让我走上正轨。

我当然很难实现这个目标。

我提出了以下似乎有效的代码。

感谢你没有给我答案作为SQL并让我真正想到关于它:)



仍然只有P1和P2将传入存储过程。

其他的将是局部变量。

BTW，P1可能是一天中的任何时间并不总是00：00：00.000这只是我的例子时间。

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

我的尝试：



Case语句的各种不同格式。

What I have tried:

Various different formats for the Case Statement.

推荐答案

你正在以艰难的方式和可扩展性最差的方式进行。您在WHERE子句中使用DATEPART，这将导致解析每个记录并将其与条件进行比较。不要这样做。



根据您的AM / PM和您想要的日期设置两个日期时间。注意你的P！参数包含日期和时间？使用它对您有利。从您获得的日期创建两个日期时间值，并根据AM或PM参数添加时间部分。



对于2016-06-09的AM，您的日期时间范围最终为2016-06-09 00：00：00.000至2016-06-09 12：00：00.000。对于PM来说，它将几乎相同，'2016-06-09 12：00：00.000'到'2016-06-10 00：00：00.000'。请注意，结束时间是第二天的午夜。这避免了必须执行11：59：59.999，这可能不完全准确。



你的WHERE子句现在性能更高，因为它不需要为每条记录做任何函数调用。它只是两个日期时间值之间的直接日期时间值比较。

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.

这篇关于Sql选择特定日期的记录，然后选择AM或PM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！