SELECT查询中的日期计算 [英] DATE CALCULATION from SELECT query

问题描述

尊敬的专业人员，

我在下面的这个问题上迷路了:

我有一个访问表，其中存储着包括出生日期在内的会员信息.
对于使用VB2010的Windows窗体应用程序，我想查询成员表"以仅选择其生日在NOW和接下来的7天之间的成员.

也就是说，彼得的生日是1982年9月27日.但是今天是2012年9月24日.
在3天的时间内，彼得将庆祝他的生日.

考虑到这一点，我创建了一个列表框控件来存储从成员的表中选择的人的姓名，这些人的出生日期在NOW和接下来的7天之间.

我只需要检索SELECT语句，就可以使用ADODB.RECORDSET对象用数据填充LIST Box控件.

我不知道如何完成此任务的SELECT查询.有人可以帮我吗?

注意:
MS Access
Windows窗体
Visual Basic2010.

Dear Professionals,

I am lost in this problem below:

I have an Access Table that stores Members Info including date of births.
With my Windows Forms Application using VB2010, I want to query the Members Table to select only members whose birth dates fall between NOW and the next 7 days.

That is, Peter''s Birth date is: 27rd Sept. 1982. But today''s date is 24th Sept. 2012.
In 3 days time, Peter will be celebrating his birth date.

With this in mind, I have created a listbox control to store the names of persons selected from the members'' table whose birth date is between NOW and next 7 days.

I only need to retrieve the SELECT statement and I can populate the LIST Box control with the data using a ADODB.RECORDSET object.

I do not know how to complete the SELECT query of this task. Please can someone help me?

NB:
MS Access
Windows Forms
Visual Basic 2010.

推荐答案

如果您想将BirthDate与某些日期范围进行比较，则需要使用一个简单的技巧:替换年份的年部分[BirthDate]与当前年份，并将其与这些日期进行比较.

但是，如何替换BirthDate中的年份部分?
在 MS SQL Server 2012 中，使用 DATEFROMPARTS [ ^ ]方法.在 MS SQL Server的先前版本中，您需要编写自定义函数以从BithDate的各个部分创建日期.例如:

If you would like to compare the BirthDate with some range of dates, you need to use a simple trick: replace the year part of [BirthDate] with current year and compare it with these dates.

But how to replace the year part in BirthDate?
In MS SQL Server 2012 use the DATEFROMPARTS[^] method. In the previous versions of MS SQL Server, you need to write custom function to create date from parts of BithDate. For example:

-- =============================================
-- Description:	Gets date from parts
-- =============================================
CREATE FUNCTION [dbo].[CREATEDATE] 
(
	-- Add the parameters for the function here
	@aYear INT = 0,
	@aMonth INT = 0,
	@aDay INT = 0
)
RETURNS DATETIME
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result DATETIME
        -- replace '-' date separator with '/' if necessary ;)
	SET @Result = '1900-01-01'

	-- Add the T-SQL statements to compute the return value here
	IF (@aYear>=1900 AND @aMonth>0 AND @aDay>0)
	BEGIN
		SELECT  @Result = DATEADD(mm,(@aYear-1900)* 12 + @aMonth - 1,0) + (@aDay-1) 
		--another method:
                --SELECT @Result = CONVERT(DATETIME,@aYear + '/' + @aMonth + '/' + @aDay) 
	END

	-- Return the result of the function
	RETURN @Result

END

最后，查询应如下所示:

And finally, the query should looks like this one:

DECLARE @begDate DATETIME
DECLARE @endDate DATETIME

--get the current date
SET @begDate = GETDATE()
--get the current date + 7 days
SET @endDate = DATEADD(d, 7, @begDate)

--if you would like to see the result dates, uncomment below command
--SELECT @begDate As [dateFrom], @endDate AS [dateTo]

--trick: replace the year part of [BirthDate] with current year and compare it with above dates
SELECT [pName], [Birthdate]
FROM PEOPLE
WHERE [dbo].CREATEDATE(YEAR(GETDATE()), MONTH([Birthdate]), DAY([Birthdate])) BETWEEN @begDate AND @endDate

在MS Access中:

In MS Access:

SELECT  [pName], [Birthdate]
FROM PEOPLE
WHERE DateSerial(Year(Date()), Month([BirthDate]) ,Day([BirthDate])) BETWEEN Date() and DateAdd("d", 7, Date());

[/EDIT]

就是这样！

[/EDIT]

That''s all!

像这样的东西会很好:

Something like this would be good:

SELECT *
FROM Birthdates
WHERE BirthDate BETWEEN now and DateAdd("d", 7, now);

在sql server中，您可以尝试如下操作:

In sql server u can try like :

select * from Test where TestDate between GETDATE() and DATEADD(DAY, 7, GETDATE())

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