如何在两个字符串日期之间使用命令

I am using following select query, but the problem is IssueDate column is in varchar and it not give correct result. Is any way to use between command string date in SQL:

我正在使用下面的select查询，但是问题是发布列在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

Output:

输出:

2 个解决方案

#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

If execution time will be very high:

如果执行时间非常长:

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

#2

Use CONVERT with to cast IssueDate to a DATE type. Also when using date constants, use the YYYYMMDD format.

使用转换将发布转换为日期类型。在使用日期常量时，也要使用YYYYMMDD格式。

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

However, if you have an index on IssueDate, this will prevent the use of it. My suggestion is to fix your schema and use the proper data type to store your values.

但是，如果您有一个关于发行的索引，这将阻止使用它。我的建议是修复您的模式并使用适当的数据类型来存储您的值。

#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