June 22, 2011 at 1:34 am
Table has below birthdates records.
say:
1980-01-02 00:00:00.000
1983-07-02 00:00:00.000
1965-03-29 00:00:00.000
1955-03-25 00:00:00.000
1947-07-28 00:00:00.000
1969-04-16 00:00:00.000
1966-11-26 00:00:00.000
1955-08-29 00:00:00.000
1945-06-27 00:00:00.000
1965-03-29 00:00:00.000
1955-03-25 00:00:00.000
1965-03-29 00:00:00.000
1955-03-25 00:00:00.000
1955-03-25 00:00:00.000
Below query showing result when from date:15 June and to date: 15 July
SELECT dt_BirthDate FROM tbl_MARC_MemberMaster WHERE
(MONTH(dt_BirthDate) * 100) + DAY(dt_BirthDate) BETWEEN (6 * 100) + 15 AND (7 * 100) + 15
Result is:
1983-07-02 00:00:00.000
1945-06-27 00:00:00.000
But when user enter birthdate like from date:22 June and to date: 5 Jan, it displayed blank result. But records are present in object.
SO query has to show result from 22 June to 5 Jan. Here I'm facing problem of year break.
Thanks in advance.
June 22, 2011 at 2:30 am
Nasty.
I've thought about the logic a little & this might work:
If @StartDay <= @EndDay then
where Day(Birthdate) Between @StartDay and @EndDay
else
where Day(Birthdate) Not Between @StartDay and @EndDay
AND
If @StartMonth <= @EndMonth then
where Month(Birthdate) Between @StartMonth and @EndMonth
else
where Month(Birthdate) Not Between @StartMonth and @EndMonth
But haven't got time to try and convert that to working SQL.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 22, 2011 at 4:48 am
Guys, Finally i got query.
SELECT dt_BirthDate FROM tbl_MARC_MemberMaster G
WHERE ISNULL(dt_BirthDate,'')<>'' AND CONVERT(VARCHAR,dt_BirthDate,101)<>'01/01/1900'
AND 1 = (FLOOR(DATEDIFF(dd,G.dt_BirthDate,CONVERT(SMALLDATETIME,'06/15/2011')+DATEDIFF(dd,CONVERT(SMALLDATETIME,'01/05/2011'),CONVERT(SMALLDATETIME,'06/15/2011'))) / 365.25))
- (FLOOR(DATEDIFF(dd,G.dt_BirthDate,CONVERT(SMALLDATETIME,'06/15/2011')) / 365.25))
Chao.
June 22, 2011 at 8:34 am
Jeff Moden has previously posted about how inefficient it is to convert datetime data to varchar. His most recent post is here http://www.sqlservercentral.com/Forums/FindPost1119797.aspx.
There is a solution that only uses the datetime functions in SQL, but it will probably need to be tweaked to account for leap years.
SELECT dt_Birthdate
FROM tbl_MARC_MemberMaster
WHERE DatePart(dy, DateAdd(Day, -DatePart(dy, @Start_Date), dt_BirthDate)) < DatePart(dy, DateAdd(Day, -DatePart(dy, @Start_Date), @End_Date))
Essentially what this does is shifts all of the dates to make @Start_Date fall on Jan 1.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 23, 2011 at 12:11 am
Thank you so much.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply