MS Access SQL Server数据库-CAST函数的查询语法 [英] MS Access SQL Server DB - Query Syntax for CAST Function

问题描述

我已将Access数据库(.mdb)转换为SQL Server.同时，在构建新的应用程序表单之前，我仍然需要使用Access作为前端.有人可以告诉我该如何解决以下情况:

I have converted an Access db (.mdb) to SQL Server. In the meantime I still need to use Access as a front end until new application forms are constructed. Can someone tell me what I might do to fix the situation where:

在Access 2007中，查询如下:

In Access 2007, a query such as:

SELECT *
FROM TransactionTotals
WHERE TransactionTotals.[Date]= Date()
ORDER BY TransactionTotals.EntryID DESC;

有效，但是由于Date()函数不适用于SQL Server，因此在上一篇文章的帮助下，正确的语法是:

worked, however since the Date() function will not work with SQL Server, with help in a previous post the correct syntax is:

SELECT *
FROM TransactionTotals
WHERE TransactionTotals.[Date]= CAST(GETDATE() AS DATE)
ORDER BY TransactionTotals.EntryID DESC;

但是！尽管上面的代码可以在直接的SQL Server查询(SQL Management Studio)中运行，但是在Access中会在WHERE子句中显示语法错误"响应.

BUT! Although the code above will work in a direct SQL Server query (SQL Management Studio), it will be tossed out in Access with a Syntax Error response on the WHERE clause.

可以在Access中完成某些操作，以便我仍然可以运行查询绑定表单.

Can something be done in Access so I can still run my query bound forms.

推荐答案

我通常完全按照您的意愿进行操作，请先访问，然后再迁移到SQL Server.与服务器类型数据库相比，Access的语法确实有些怪异，尤其是在涉及JOIN子句和日期时，GETDATE()仅适用于SQL Server，请在Access中尝试一下...

I usually do exactly what you do, Access first before migration to SQL server. Access has some really weird syntax compared to server type databases especially when it comes to JOIN clauses and dates, GETDATE() only works on SQL Server, in Access, try this...

SELECT *
FROM TransactionTotals
WHERE TransactionTotals.[Date]= Format(NOW(),"General Date")
ORDER BY TransactionTotals.EntryID DESC;

随时更改日期的格式(带或不带时间).

Feel free to change the format of the date, with or without time.

这篇关于MS Access SQL Server数据库-CAST函数的查询语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！