September 18, 2012 at 2:37 pm
I can't figure out what is wrong with this code.
Please help. It makes sense to me, but must be missing something simple.
should give me everyone who has a birthday in the next 30 days...
Thanks in advance
select m.fname + ' ' + m.lname from members m
where datediff(dd, cast(datepart(mm,m.dob) + '/' + datepart(dd,m.dob) + '/' + datepart(yyyy,getdate())) as date, getdate()) <= 30
September 18, 2012 at 2:41 pm
Try this:
select
m.fname + ' ' + m.lname
from
members m
where
m.dob >= cast(getdate() as date) and
m.dob < dateadd(dd, 31, cast(getdate() as date));
Edit: Misread what was needed. hang on.
September 18, 2012 at 2:59 pm
Fixed the code:
declare @members table (mid int, dob date, fname varchar(10), lname varchar(10));
insert into @members
values (1,'1959-08-29','a','a'),(2,'1959-09-29','b','a'),(3,'1959-10-29','c','a'),(4,'2011-09-20','a','d');
select
m.fname + ' ' + m.lname
from
@members m
where
m.dob >= dateadd(yy, -1 * datediff(yy,m.dob,getdate()), cast(getdate() as date)) and
m.dob < dateadd(yy, -1 * datediff(yy,m.dob,getdate()), dateadd(dd, 31, cast(getdate() as date)));
September 18, 2012 at 3:19 pm
I got it thanks. I had the cast statements wrong. I don't know if this is more complicated but I figured it out.
SELECT MemberID, FirstName + ' ' + LastName AS memname, CAST(CAST(DATEPART(mm, DOB) AS varchar) +
'/' + CAST(DATEPART(dd, DOB) AS varchar) AS varchar) AS birthday
FROM dtMembers AS m
WHERE
(CAST(CAST(DATEPART(mm, DOB) AS varchar) + '/' + CAST(DATEPART(dd, DOB) AS varchar) + '/' + CAST(DATEPART(yyyy, GETDATE()) AS varchar) AS date)
> GETDATE()) AND (CAST(CAST(DATEPART(mm, DOB) AS varchar) + '/' + CAST(DATEPART(dd, DOB) AS varchar) + '/' + CAST(DATEPART(yyyy, GETDATE()) AS varchar)
AS date) < DATEADD(dd, 30, GETDATE()))
ORDER BY DATEPART(mm, DOB), DATEPART(dd, DOB)
I pasted it into here, and it looks very sloppy, but it works correctly. Thanks, you helped me on the cast statements and I forgot the other date.
September 23, 2012 at 3:55 pm
SELECT M.last_name, M.first_name
FROM Members AS M
WHERE M.birth_date BETWEEN CAST(CURRENT_TIMESTAMP AS DATE)
AND DATEADD (DD, 30, CAST(CURRENT_TIMESTAMP AS DATE));
Thank you for cleaning it up, and the CURRENT_TIMESTAMP tip. Also, this query is a query on the client (VB.net).
However, I still have to convert the member's birth_date year to the current year or something because birth_date is still in the past because of the year. What is the best and cleanest way to accomplish this?
September 24, 2012 at 2:13 am
Not sure if this is the "best" way but it may work for you:
declare @members table (mid int, birth_date date, first_name varchar(10), last_name varchar(10));
insert into @members
values (1,'1959-08-29','a','a'),(2,'1959-09-29','b','a'),(3,'1959-10-29','c','a'),(4,'2011-09-20','a','d');
SELECT M.last_name, M.first_name, [Birth date this year]
FROM @Members AS M
CROSS APPLY (
(SELECT CAST(STUFF(
CONVERT(VARCHAR(10), birth_date, 126)
,1
,4
,CONVERT(VARCHAR(4), CURRENT_TIMESTAMP, 126)
) AS DATE))) a([Birth date this year])
WHERE a.[Birth date this year] BETWEEN CAST(CURRENT_TIMESTAMP AS DATE)
AND DATEADD (DD, 30, CAST(CURRENT_TIMESTAMP AS DATE));
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply