如何使用动态日期时间Pararmeter从Sql Server中选择记录 [英] How Do I Select Record From Sql Server Using Dynamic Datetime Pararmeter

问题描述

Hello Everyone，



我试图根据用户日期选择从sql server中选择特定记录，用户的日期格式为以下格式：dd / mm / yyyy来自Ajax calenda extender，我使用getdate（）在表格中存储日期，即表格中的日期格式为以下格式：2015-03-16 08：36：08.330。



我试过这个：

Hello Everyone,

I am trying to select specific record from sql server based on user date selection, date format for users is in this format:dd/mm/yyyy from Ajax calenda extender, and i use getdate() to store date in the table, i.e. date format in the table is in this format:2015-03-16 08:36:08.330.

I have tried this:

ALTER PROCEDURE [dbo].[SelectRCOByDate] 
	-- Add the parameters for the stored procedure here
	@Username Varchar (50)=NULL,
	@AttndDate DateTime = NULL
AS
IF @AttndDate is null
SET @AttndDate = CONVERT(VARCHAR, getdate(), 103)
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	SELECT r.Username AS Username, r.surname AS Surname, r.firstname AS Firstname,u.branch AS Branch, r.RTime AS ResumptionTime,r.CTime AS ClosureTime, CONVERT(VARCHAR, r.ATTNDDate, 103) AS ATTNDATE
	FROM RCORegister r
	INNER JOIN UserMgmt u ON r.Username = u.Username
	WHERE  CONVERT(VARCHAR, r.ATTNDDate,103)  = @AttndDate 
	ORDER BY  r.ATTNDDate DESC
 
END

但是我遇到了这个错误：

将数据类型varchar转换为datetime时出错。



请问我做错了什么？在此先感谢。

but i am having this error:
Error converting data type varchar to datetime.

Please is there anything i have done wrong? Thanks in advance.

推荐答案

不要将日期转换为 varchar 。



看起来你正试图忽略时间部分，并检索特定日期的所有记录。如果您使用的是Microsoft SQL Server 2008或更高版本，则可以将列和参数更改为 日期类型 [ ^ ]，不存储时间。



否则，您需要查找开始之间的所有日期和指定日期结束：

Don't convert dates to varchar.

It looks like you're trying to ignore the time part, and retrieve all records for a particular date. If you're using Microsoft SQL Server 2008 or higher, you could change the column and the parameter to the date type[^], which doesn't store the time.

Otherwise, you'll need to look for all dates between the start and end of the specified date:

IF @AttndDate Is Null SET @AttndDate = GetUtcDate();
SET @AttndDate = DateAdd(dd, 0, DateDiff(dd, 0, @AttndDate));

SELECT 
    r.Username AS Username, 
    r.surname AS Surname, 
    r.firstname AS Firstname,
    u.branch AS Branch, 
    r.RTime AS ResumptionTime,
    r.CTime AS ClosureTime, 
    DateAdd(dd, 0, DateDiff(dd, 0, r.ATTNDDate)) AS ATTNDATE
FROM 
    RCORegister r
    INNER JOIN UserMgmt u ON r.Username = u.Username
WHERE
    r.ATTNDDate >= @AttndDate
And
    r.ATTNDDate < DateAdd(day, 1, @AttndDate)
ORDER BY
    r.ATTNDDate DESC

由于您将日期存储为DB中的DATETIME值（或者在其上使用CONVERT没有任何意义），并将比较值作为DATETIME传递，为什么不把它留在原处并直接检查实际值？

Since you store the date as a DATETIME value in the DB (or there would be no point in using CONVERT on it), and pass the comparison value as a DATETIME as well, why not just leave it where it is and check the actual value directly?

...
INNER JOIN UserMgmt u ON r.Username = u.Username
WHERE  r.ATTNDDate = @AttndDate
ORDER BY  r.ATTNDDate DESC

你永远不要涉及str进入日期比较。它很慢，容易出错并且很难调试。



以下都将为您提供选择：



DATEADD（dd，DATEDIFF（dd，0，r.ATTNDDate），0）将只给你一个日期

将

CONVERT（日期， r.ATTNDDate） - 这假定SQL Server 2008或更高版本





你的比较可能是：



DATEADD（dd，DATEDIFF（dd，0，r.ATTNDDate），0）= @AttNDDate如果你的变量是纯日期（即时间分量为零）



根据需要更改比较运算符...



如果在您的表中，您有整个范围，那么您可以做

r.ATTNDDate BETWEEN @AttNDDate和DateAdd（d，1，@ AttNDDate）





你可以如果您不需要数据库中的时间组件，则可以进一步简化此操作。只需将insert语句更改为如上所示强制转换GetDate（）以获取日期组件（当然，您必须对现有数据运行更新）。通过这种方式，您可以进行简单明了的比较

r.ATTNDDate = @ATTNDDate



我希望这会有所帮助。祝你好运。

You should never involve strings into date comparisons. It is slow, easy to get wrong and hard to debug.

The following will all give you options for your select:

DATEADD(dd, DATEDIFF(dd, 0, r.ATTNDDate), 0) will give you date only
as will
CONVERT(DATE, r.ATTNDDate) - this assumes SQL Server 2008 or higher


You comparison could be:

DATEADD(dd, DATEDIFF(dd, 0, r.ATTNDDate), 0) = @AttNDDate if your variable is "pure" date (that is time component is zero)

Change the comparison operator as needed...

If, in your table, you have whole range of times then you could do
r.ATTNDDate BETWEEN @AttNDDate AND DateAdd(d, 1, @AttNDDate )


You could simplify this even further if you don't need time component in the database. Just change your insert statement to cast GetDate() as above to get only date component (and you'll have to run an update on the existing data of course). This way you can do plain and simple comparison
r.ATTNDDate = @ATTNDDate

I hope this helps. Good luck.

这篇关于如何使用动态日期时间Pararmeter从Sql Server中选择记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！