gete date between two date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

gete date between two date

Author

Topic

ghugeramesh
Starting Member

4 Posts

Posted - 2011-06-08 : 06:09:25

i want to get birth date between of previous 15 day and next 15 day of current date.

if current date is 6/8/2011 then my query should show result

result date : 25/7/2010 , 25/7/2009, 25/7/2003 ,15/8/2002 ,15/8/2007

it should contain day of previous 15 day and next 15 day of current date there should not issue of year.

my query is :

SELECT alumni_id,email
FROM tblalumni_member WHERE
convert(datetime,Birth_Date, 100) between (Dateadd(day,-15,GETDATE())) AND (Dateadd(day,15,GETDATE()))
ORDER BY Day(Birth_Date)

please reply.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-08 : 06:23:06

what is the data type for Birth_Date ?

KH
[spoiler]Time is always against us[/spoiler]

ghugeramesh
Starting Member

4 Posts

Posted - 2011-06-08 : 06:31:34

datetime is datatype of birthdate

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-08 : 06:37:21

then there isn't a need to convert at all


WHERE Birth_Date >= dateadd(day, datediff(day, 0, getdate()), -15)
and   Birth_Date <= dateadd(day, datediff(day, 0, getdate()),  15)

KH
[spoiler]Time is always against us[/spoiler]

ghugeramesh
Starting Member

4 Posts

Posted - 2011-06-08 : 06:45:07

thanks for reply.

but there is still problem means it just give the resulted date of current year.

I want

if current date is 6/8/2011 then my query should show result

result date : 25/7/2010 , 25/7/2009, 25/7/2003 ,15/8/2002 ,15/8/2007

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-06-08 : 06:52:23

The obvious way is something like:


SELECT alumni_id, email 
FROM tblalumni_member
WHERE MONTH(Birth_Date) * 100 + DAY(Birth_Date)
	BETWEEN MONTH(DATEADD(day, -15, CURRENT_TIMESTAMP)) * 100 + DAY(DATEADD(day, -15, CURRENT_TIMESTAMP))
	AND MONTH(DATEADD(day, 15, CURRENT_TIMESTAMP)) * 100 + DAY(DATEADD(day, 15, CURRENT_TIMESTAMP))
ORDER BY MONTH(Birth_Date) * 100 + DAY(Birth_Date)

However, as functions on a column stop the use of an index (Google SARGABLE)
it may be more efficient to use a number/tally table. (Google Tally Table)


;WITH CurrentYear
AS
(
	SELECT DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -15) AS StartDate
		,DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 15) AS EndDate
)
,Numbers
AS
(
	SELECT [number]
	FROM [master].dbo.spt_values
	WHERE [type] = 'P'
		-- assuming no-one aged over 120
		AND [number] <= 120
)
,YearRanges
AS
(
	SELECT DATEADD(year, -N.number, C.StartDate) AS StartDate
		,DATEADD(year, -N.number, C.EndDate) AS EndDate
	FROM CurrentYear C
		CROSS JOIN Numbers N
)
SELECT A.alumni_id, A.email 
FROM tblalumni_member A
	JOIN YearRanges Y
		ON A.Birth_Date BETWEEN Y.StartDate AND Y.EndDate
ORDER BY MONTH(A.Birth_Date) * 100 + DAY(A.Birth_Date)

ghugeramesh
Starting Member

4 Posts

Posted - 2011-06-08 : 07:04:35

thanks lot now its work fine.

Thaks Ifor.
Thanks khtan.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources