查询每周生日 [英] Query for weekly birthday

问题描述

你好，

我有一个查询要从数据库中检索mont的生日:

I have a query to retrieve the mont's birthday, from database:

            Dim ttt = Rep_Entity.membros.Where(
                Function(u) Month(u.datanasc) = Month(Now).ToString).[Select](
                Function(rpt) New With {
                    Key .nome = rpt.nome,
                    Key .datanasc = rpt.datanasc
                        })

我希望编写类似的查询来检索一周中的生日.我该怎么办?

I wish to write a similar query to retrieve the birthday of the week. How could I do that?

非常感谢.

Valdirnm

推荐答案

你好，

好的，昨天晚上我的回复完全不对，所以这是我目前的建议.

Okay, I had a response from last night that was totally off so here is my current suggestion.

我创建了下表并填充了它

I created the following table and populated it

CREATE TABLE People 
	(
		PK int IDENTITY(1,1) NOT NULL PRIMARY KEY,
		FullName varchar(30) NOT NULL,
		dob datetime NULL
	)
GO
 
INSERT INTO People (FullName, dob) VALUES ('John Smith', '1965-02-28')
INSERT INTO People (FullName, dob) VALUES ('Alex Black', '1960-02-29')
INSERT INTO People (FullName, dob) VALUES ('Bill Doors', '1968-02-27')
INSERT INTO People (FullName, dob) VALUES ('Sam Jobless', '1958-03-01')
INSERT INTO People (FullName, dob) VALUES ('Nancy Davolio', '1968-12-08')
INSERT INTO People (FullName, dob) VALUES ('Andrew Fuller', '1952-02-19')
INSERT INTO People (FullName, dob) VALUES ('Janet Leverling', '1963-08-30')
INSERT INTO People (FullName, dob) VALUES ('Margaret Peacock', '1958-09-19')
INSERT INTO People (FullName, dob) VALUES ('Steven Buchanan', '1955-03-04')
INSERT INTO People (FullName, dob) VALUES ('Michael Suyama', '1963-07-02')
INSERT INTO People (FullName, dob) VALUES ('Robert King', '1960-05-29')
INSERT INTO People (FullName, dob) VALUES ('Laura Callahan', '1958-01-09')
INSERT INTO People (FullName, dob) VALUES ('Anne Dodsworth', '1969-07-02')
INSERT INTO People (FullName, dob) VALUES ('Jack Lighthouse', NULL)

创建了一个存储过程

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[uspBirthdaysInRange]
(
    @EndDate AS DATETIME,
    @StartDate AS DATETIME
) AS
BEGIN
SELECT FullName, DATEPART(MONTH, dob) AS Month, DATEPART(DAY, dob) AS Day, CONVERT(VARCHAR(10), dob, 111) AS dob
	FROM People
	WHERE	DATEADD(YEAR, DATEDIFF(YEAR,  dob, @StartDate), dob) BETWEEN @StartDate AND @EndDate
	     OR DATEADD(YEAR, DATEDIFF(YEAR,  dob, @EndDate), dob) BETWEEN @StartDate AND @EndDate
	ORDER BY CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR,  dob, @StartDate), dob) BETWEEN @StartDate AND @EndDate THEN 1 ELSE 2 END, 
		DATEPART(MONTH, dob), DATEPART(DAY, dob)

	RETURN
END


GO

创建了一个模型，该模型指示包括存储过程.由于存储过程和表具有相同的字段，因此我们返回相同的Person类型.要为EF执行的代码.

Created a model, indicated to include the stored procedure. Since the Stored procedure and table have the same fields we return the same type Person. Code to execute for EF.

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Using entity As New ForumExamplesEntities

            Dim startDate = #02/02/2017#
            Dim endDate = #02/28/2017#

            Dim birthdayResults As List(Of uspBirthdaysInRange_Result) = entity.uspBirthdaysInRange(endDate, startDate).ToList

            If birthdayResults.Count > 0 Then
                Console.WriteLine(

"{startDate}和{endDate}之间的生日") 对于每一个项目，作为birthResults中的uspBirthdaysInRange_Result Console.WriteLine(

"Birthdays between {startDate} and {endDate}") For Each item As uspBirthdaysInRange_Result In birthdayResults Console.WriteLine(

"名称:{item.FullName，15}月份:{item.Month}天:{item.Day} DOB:{item.dob}") 下一个 别的 Console.WriteLine(

"Name: {item.FullName,15} Month: {item.Month} Day: {item.Day} DOB: {item.dob}") Next Else Console.WriteLine(

这篇关于查询每周生日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！