Sql选择特定日期的记录，然后选择AM或PM

本文介绍了Sql选择特定日期的记录，然后选择AM或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.

Sql选择特定日期的记录

Sql选择特定日期的记录，然后选择AM或PM

问题描述

推荐答案