选择查询中的SQL日期格式问题

I have an ASP page which will fetch records from a SQL server DB table. The table "order_master" has a field called order_date. I want to frame a select query to fetch order date > a date entered by user(ex : 07/01/2008)

我有一个ASP页面,它将从SQL服务器数据库表中获取记录。表“order_master”有一个名为order_date的字段。我想构建一个选择查询以获取订单日期>用户输入的日期(例如:07/01/2008)

I tried with convert and cast, but both are not working. The sample data in order_date column is 4/10/2008 8:27:41 PM. Actually, I dont know what type it is (varchar/datetime).

我尝试使用转换和强制转换,但两者都不起作用。 order_date列中的示例数据是4/10/2008 8:27:41 PM。实际上,我不知道它是什么类型(varchar / datetime)。

Is there any way to do that?

有没有办法做到这一点?

6 个解决方案

#1

I'd check to make sure that the SQL datatype is a DateTime or SmallDateTime first, then I'd check to make sure that you're passing in a Date/DateTime value from the page.

我先检查以确保SQL数据类型是DateTime或SmallDateTime,然后我会检查以确保您从页面传入Date / DateTime值。

If those are both correct, then you'd probably be better off following Joel's advice and explicitly convert both values to dates before trying the comparison. Also, check the precision of the time values that you're looking at; it seems obvious, but 1/1/2008 12:00:00.001 AM will not be equal to 1/1/2008 12:00:00.000 AM. Yes, I am speaking from experience. :P

如果这些都是正确的,那么你可能会更好地遵循Joel的建议并在尝试比较之前将两个值显式转换为日期。另外,检查您正在查看的时间值的精度;这似乎是显而易见的,但是1/1/2008 12:00:00.001 AM将不等于1/1/2008 12:00:00.000 AM。是的,我是根据经验说的。 :P

#2

the 07/01/2008 date is the British/French annotation, so all you need to do is:

2008年7月1日的日期是英国/法国的注释,所以你需要做的就是:

SELECT myColumn FROM myTable WHERE myDateField >= convert(datetime, '07/01/2008 00:00:00', 103)

SELECT myColumn FROM myTable WHERE myDateField> = convert(datetime,'07 / 01/2008 00:00:00',103)

this code will get all rows where myDateField has the date 7th of January 2008, since 00:00:00 (hh:mm:ss) so, the first second on that day... in simple words, the entire day.

此代码将获取myDateField具有日期为2008年1月7日的所有行,从00:00:00开始(hh:mm:ss)所以,那天的第一秒......简单来说就是整天。

for more info, check Books online on MSDN

有关详细信息,请在MSDN上在线查看图书

#3

You could create a stored procedure like this

您可以创建这样的存储过程

CREATE PROCEDURE GetOrders
    @OrderDate DATETIME
AS
SELECT
    *
FROM order_master
WHERE Order_Date > @OrderDate

GO

Then you can just convert the users input to a date before calling the stored procedure via your ASP code.

然后,您可以通过ASP代码将用户输入转换为日期,然后再调用存储过程。

Edit

I just noticed the remark about the column type, you can run this command

我刚刚注意到有关列类型的注释,您可以运行此命令

sp_help order_master

to get column information to find the data type of order_date.

获取列信息以查找order_date的数据类型。

#4

Have you tried CONVERT()'ing both values to a datetime type?

您是否尝试过CONVERT()将两个值都转换为日期时间类型?

#5

Remember that when comparing dates, 4/10/2008 8:27:41 PM is not equal to 4/10/2008. SQL Server will interpret 4/10/2008 to mean 4/10/2008 12:00:00 AM, and do an exact comparison down to the second. Therefore 4/10/2008 is LESS THAN 4/10/2008 8:27:41 PM

请记住,在比较日期时,4/10/2008 8:27:41 PM不等于4/10/2008。 SQL Server将4/10/2008解释为4/10/2008 12:00:00 AM,并进行精确比较,直到第二个。因此,2008年4月10日不到4/10/2008 8:27:41 PM

#6

You state you don't know the field type. That would be the first problem to solve, find out. You can do that with:-

您声明您不知道字段类型。这将是第一个要解决的问题,找出答案。你可以这样做: -

SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Order_Master' AND
    COLUMN_NAME = 'Order_Date'

If its not one of the datetime types it should be converted, if that isn't your responsibility then get on to someone who does have the responsibility.

如果它不是日期时间类型之一,那么它应该转换,如果这不是你的责任,那么请找一个有责任的人。

The fact that you are concerned about the 'format' of the date indicates that you may be building the SQL using concatenation. If so stop doing that. Use a command with a parameter and pass in the date as date type.

您担心日期的“格式”这一事实表明您可能正在使用串联构建SQL。如果是这样就停止这样做。使用带参数的命令并将日期作为日期类型传递。

Now your issue is one of how the date is entered at the client end and getting it into an unambigous format that can be parsed as a date in the ASP code.

现在您的问题是如何在客户端输入日期并将其转换为可以在ASP代码中解析为日期的无关格式。

If that is not something you have solved add a comment to this answer and I'll expand this answer.

如果那不是您已解决的问题,请在此答案中添加评论,我将扩展此答案。

#1