sql - 如何在 2 字符串日期之间使用命令

Question

我正在使用以下选择查询，但问题是 IssueDate 列在 varchar 中，它没有给出正确的结果。在 SQL 中的命令字符串日期之间使用任何方式:

SELECT Pkid AS [Bill Id],
       InvoiceNumber,
       InvoiceType,
       IssueDate,
       FinalTotal
FROM tblInvoiceMain
WHERE IssueDate BETWEEN '01 June 2016' AND '06 June 2016'
ORDER BY Pkid DESC

输出:

Answer 1

SELECT Pkid AS [Bill Id],
       InvoiceNumber,
       InvoiceType,
       IssueDate,
       FinalTotal
FROM tblInvoiceMain
WHERE CAST(IssueDate AS DATE) BETWEEN '20160601' AND '20160606'
ORDER BY Pkid DESC

如果执行时间会非常长:

ALTER TABLE dbo.tblInvoiceMain
    ADD IssueDate2 AS CAST(IssueDate AS DATE)
GO
CREATE NONCLUSTERED INDEX ix
    ON dbo.tblInvoiceMain (IssueDate2)
    INCLUDE (Pkid, InvoiceNumber, InvoiceType, IssueDate, FinalTotal)
GO

SELECT Pkid AS [Bill Id],
       InvoiceNumber,
       InvoiceType,
       IssueDate,
       FinalTotal
FROM tblInvoiceMain
WHERE IssueDate2 BETWEEN '20160601' AND '20160606'
ORDER BY Pkid DESC