使用sql和vb.net在两个日期值之间选择数据

rqenqsqc  于 2021-07-24  发布在  Java
关注(0)|答案(2)|浏览(319)

我编写下面的sql语句是为了使用vb.net从一个特定的表中获得一个和值。但是,我一直得到以下错误
itcode是一个变量

Dim strSql As String = "SELECT sum(purchase_qty) FROM TB_STOCK WHERE it_code='" & itCode & "' AND added_date BETWEEN #07/21/2020 00:00:00# AND #07/21/2020 23:59:59#"

错误:system.data.sqlclient.sqlexception:“00附近的语法不正确。”

eufgjt7s

eufgjt7s1#

我很确定SQLServer不理解这样的日期文字。这对access有用,但不同的数据库需要不同的语法。您应该使用yyyy-mm-dd格式的文本文字:

"' AND added_date BETWEEN '2020-07-21 00:00:00' AND '2020-07-21 23:59:59'"

也就是说,我猜您将不得不在某个时候改变这些日期,所以您可能应该使用参数。您肯定应该为 it_code 价值,例如。

Dim sql = "SELECT sum(purchase_qty) FROM TB_STOCK WHERE it_code = @it_code AND added_date BETWEEN @added_date_start AND @added_date_end"
Dim command As New SqlCommand(sql, connection)

With command.Parameters
    .Add("@it_code", SqlDbType.VarChar, 50).Value = itCode
    .Add("@added_date_start", SqlDbType.DateTime).Value = myDate
    .Add("@added_date_end", SqlDbType.DateTime).Value = myDate.AddDays(1).AddSeconds(-1)
End With

我同意rodney ellis不使用 BETEWEEN 我也是。我会这样做:

Dim sql = "SELECT sum(purchase_qty) FROM TB_STOCK WHERE it_code = @it_code AND added_date >= @added_date_start AND added_date < @added_date_end"
Dim command As New SqlCommand(sql, connection)

With command.Parameters
    .Add("@it_code", SqlDbType.VarChar, 50).Value = itCode
    .Add("@added_date_start", SqlDbType.DateTime).Value = myDate
    .Add("@added_date_end", SqlDbType.DateTime).Value = myDate.AddDays(1)
End With

最后两个例子都假设 myDate 时间已经归零了。如果没有,你可以用 myDate.Date .

bnl4lu3b

bnl4lu3b2#

Dim strSql As String = "SELECT sum(purchase_qty) FROM TB_STOCK 
WHERE it_code='" & itCode & "' AND added_date BETWEEN '2020-07-20 00:00:00' AND '2020-07-21 23:59:59'"

另外,不要在-https://www.mssqltips.com/sqlservertutorial/9316/sql-server-between-dates-issue/

相关问题