有没有办法让我把日期持有者置于日期并让SQL了解它是一个约会？ [英] Is there a way I can put place holders in a date and have SQL understand that it's a date?

问题描述

Using sqlconn As New SqlConnection("sqlstring")
                Using da As New SqlDataAdapter(String.Empty, sqlconn)
                    Dim death As New StringBuilder("")
                    Dim Cemetery As New StringBuilder("")
                    Dim sql As New StringBuilder("Select profile.Individual_ID, First_name, Middle_name, Last_name, Sex, Race, Place_of_birth, County_of_birth, State_of_birth, Date_of_birth") 'inner join death on death.individual_ID = profile.individual_ID
                    Dim wherestatement As New StringBuilder(" WHERE 1 = 1 ")

 If Not String.IsNullOrEmpty(Birthyearstart.Text) AndAlso Not String.IsNullOrEmpty(Birthyearend.Text) Then
                        wherestatement.Append(" And Year(Date_of_birth) Between Year(@StartYear) And Year(@EndYear)")
                        da.SelectCommand.Parameters.AddWithValue("@StartYear", Birthyearstart.Text)
                        da.SelectCommand.Parameters.AddWithValue("@EndYear", Birthyearend.Text)
                    End If

 sql.Append(" from profile ")
                    sql.Append(death)
                    sql.Append(Cemetery)
                    sql.Append(wherestatement)
                    '   da.SelectCommand.CommandText = sql.ToString() + " Order by Last_name ASC;"
                    da.SelectCommand.CommandText = sql.ToStr

我存储日期00/00/1901当我不知道一个月或一天或两者。但是，当我运行我的代码时，我没有返回日期结构的行。如果我输入实际日期，例如01/01/1901，它将返回与该日期有关的所有记录。



有没有办法使用占位符并仍过滤年份。



我是什么尝试过：



我试图做另一个展示位置持有者并且仍然得到相同的结果。

I am storing the dates as such 00/00/1901 when I do not know the month or day or both. However, when I run my code I am getting no rows returned for the date structure. If I put an actual date such as 01/01/1901 it will return all records pertaining to that date.

Is there a way to use placeholders and still filter the year.

What I have tried:

I tried to do another placement holder and still got the same result.

推荐答案

除了评论中提到的查询问题之外，我能看到你的概念工作的唯一方法是将日，月和年存储在单独的可空列中。然后在where子句中使用year_of_birth列

Apart from the problems with your query as mentioned in the comments, the only way I can see your concept working is to store the day, month and year in separate nullable columns. Then just use the year_of_birth column in your where clause

Year_of_birth Between @StartYear And @EndYear

或者将有效的Date_of_Birth作为必填字段

Or make a valid Date_of_Birth a compulsory field

这篇关于有没有办法让我把日期持有者置于日期并让SQL了解它是一个约会？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！