如何通过 ssql 中的计算列计算下一个生日

Question

我想计算人们的下一个生日 在我的数据库中，我有一个名为"出生日期"的列，其他称为"生日" 我希望能够使用 SSL 中的计算列计算生日。

(datepart(year，getdate(((&datepart(day，[DateofBirth]((

我建议我可以通过数据表务实地做到这一点，但我得到一个错误。 下面是我的代码。

Dim birthda As New SqlDataAdapter(birthcmd(

Dim birthdt As New DataTable
birthda.Fill(birthdt)
For Each rw As DataRow In birthdt.Rows
Dim dob As String = rw.Item(3)

Dim mdat As Date = FormatDateTime(dob, DateFormat.ShortDate)
Dim bday As Date = (Date.Today.Year & mdat.Month & mdat.Day)

Dim yers As Integer = DateDiff(DateInterval.Year, mdat.Date, Today.Date)
Dim moths As Integer = DateDiff(DateInterval.Month, mdat.Date, Today.Date)
Dim dys As Integer = DateDiff(DateInterval.Day, mdat.Date, Today.Date)

Answer 1

请尝试以下命令：

birthcmd.CommandText = "SELECT        DateofBirth, " & _
"DATEADD(year, DATEDIFF(year, DateofBirth, { fn NOW() }) + (CASE WHEN month(dateofbirth) * 100 + day(dateofbirth) < month({ fn NOW() }) * 100 + day({ fn NOW() }) THEN 1 ELSE 0 END), DateofBirth) AS Nextbday, " & _
"DATEDIFF(year, { fn NOW() }, DATEADD(year, DATEDIFF(year, DateofBirth, { fn NOW() }) + (CASE WHEN month(dateofbirth) * 100 + day(dateofbirth) < month({ fn NOW() }) * 100 + day({ fn NOW() }) THEN 1 ELSE 0 END), DateofBirth)) AS Diffyers, " & _
"DATEDIFF(month, { fn NOW() }, DATEADD(year, DATEDIFF(year, DateofBirth, { fn NOW() }) + (CASE WHEN month(dateofbirth) * 100 + day(dateofbirth) < month({ fn NOW() }) * 100 + day({ fn NOW() }) THEN 1 ELSE 0 END), DateofBirth)) AS Diffmoths, " & _
"DATEDIFF(day, { fn NOW() }, DATEADD(year, DATEDIFF(year, DateofBirth, { fn NOW() }) + (CASE WHEN month(dateofbirth) * 100 + day(dateofbirth) < month({ fn NOW() }) * 100 + day({ fn NOW() }) THEN 1 ELSE 0 END), DateofBirth)) AS Diffdys " & _
"FROM myBirthdayTable"