May 20, 2013 at 2:30 pm
This will be an easy challenge.
Select date_of_birth from person
--This gives values in the YYYYMMDD format like 19700101
I want a list of people whose birthdays are within the next 30 days
from today. Can someone construct the SQL for me please ?
May 20, 2013 at 2:44 pm
mw112009 (5/20/2013)
This will be an easy challenge.Select date_of_birth from person
--This gives values in the YYYYMMDD format like 19700101
I want a list of people whose birthdays are within the next 30 days
from today. Can someone construct the SQL for me please ?
Would help to know more. What is the data type of the column date_of_birth?
May 20, 2013 at 3:02 pm
The type of the date_of_birth column is char(8)
May 21, 2013 at 1:05 am
HOPE THIS HELP
DECLARE @TABLE_NAME TABLE(
NAME VARCHAR(30),
BDAY CHAR(8))
INSERT INTO @TABLE_NAME(NAME,BDAY)
VALUES('A','20130522')
INSERT INTO @TABLE_NAME(NAME,BDAY)
VALUES('B','20130523')
INSERT INTO @TABLE_NAME(NAME,BDAY)
VALUES('C','20130524')
INSERT INTO @TABLE_NAME(NAME,BDAY)
VALUES('D','20130525')
INSERT INTO @TABLE_NAME(NAME,BDAY)
VALUES('E','20130521')
INSERT INTO @TABLE_NAME(NAME,BDAY)
VALUES('F','20130620')
SELECTNAME,
DATEDIFF(DAY,GETDATE(), CAST(BDAY AS DATETIME)) [BDAY WITHIN 30 DAYS]
FROM@TABLE_NAME
WHEREDATEDIFF(DAY,GETDATE(), CAST(BDAY AS DATETIME)) BETWEEN 1 AND 30
May 21, 2013 at 1:15 am
eklavu (5/21/2013)
HOPE THIS HELPDECLARE @TABLE_NAME TABLE(
NAME VARCHAR(30),
BDAY CHAR(8))
INSERT INTO @TABLE_NAME(NAME,BDAY)
VALUES('A','20130522')
INSERT INTO @TABLE_NAME(NAME,BDAY)
VALUES('B','20130523')
INSERT INTO @TABLE_NAME(NAME,BDAY)
VALUES('C','20130524')
INSERT INTO @TABLE_NAME(NAME,BDAY)
VALUES('D','20130525')
INSERT INTO @TABLE_NAME(NAME,BDAY)
VALUES('E','20130521')
INSERT INTO @TABLE_NAME(NAME,BDAY)
VALUES('F','20130620')
SELECTNAME,
DATEDIFF(DAY,GETDATE(), CAST(BDAY AS DATETIME)) [BDAY WITHIN 30 DAYS]
FROM@TABLE_NAME
WHEREDATEDIFF(DAY,GETDATE(), CAST(BDAY AS DATETIME)) BETWEEN 1 AND 30
This will work only if the year of birth is the current year
It will not work for dates like 19700101
Edited:Corrected some spelling mistakes
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 21, 2013 at 1:28 am
One way to do this
DECLARE@person TABLE
(
person_name VARCHAR(50),
date_of_birth CHAR(8)
)
DECLARE@start_date CHAR(4)
DECLARE @end_date CHAR(4)
INSERT@person
SELECT 'A', '19800101' UNION ALL
SELECT 'B', '19700605' UNION ALL
SELECT 'C', '19650525' UNION ALL
SELECT 'D', '19901020'
SET@start_date = RIGHT(CONVERT(CHAR(8),CURRENT_TIMESTAMP,112),4)
SET@end_date = RIGHT(CONVERT(CHAR(8),DATEADD(DAY,29,CURRENT_TIMESTAMP),112),4)
SELECT*
FROM@person
WHERERIGHT(date_of_birth,4) BETWEEN @start_date AND @end_date
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 21, 2013 at 8:31 am
First, run the following query against your data:
select * from dbo.yourTableNameHere where isdate(date_of_birth) = 0;
If this returns any records you have dates that aren't dates and those need to be fixed.
May 21, 2013 at 8:41 am
Then try this when all the dates are valid:
DECLARE @TABLE_NAME TABLE(
NAME VARCHAR(30),
BDAY CHAR(8))
INSERT INTO @TABLE_NAME(NAME,BDAY)
VALUES('A','19901022')
INSERT INTO @TABLE_NAME(NAME,BDAY)
VALUES('B','19940523')
INSERT INTO @TABLE_NAME(NAME,BDAY)
VALUES('C','19980602')
INSERT INTO @TABLE_NAME(NAME,BDAY)
VALUES('D','19590525')
INSERT INTO @TABLE_NAME(NAME,BDAY)
VALUES('E','20130521')
INSERT INTO @TABLE_NAME(NAME,BDAY)
VALUES('F','20130620');
select
*
from
@TABLE_NAME
where
cast(BDAY as DATE) between dateadd(year, - datediff(year, cast(BDAY as DATE), getdate()), cast(getdate() as DATE))
and dateadd(year, - datediff(year, cast(BDAY as DATE), getdate()), dateadd(day, 30, cast(getdate() as DATE)));
GO
May 21, 2013 at 10:29 am
I couldn't see any difference in CPU or Query cost between my answer and Lynn's answer when doing <10 records.
It's all down to preference.
DECLARE @FictionalDate DATE = '1969-12-15'
DECLARE @bdays TABLE (Birthday CHAR(8))
INSERT @bdays
(Birthday)
VALUES ('20010101'),
('19700102'),
('19700101'),
('19951201'),
('20130521'),
('20130620')
SELECT CONVERT(DATE,Birthday)
FROM @bdays
WHERECONVERT(INT,CONVERT(DATETIME,Birthday)) - CONVERT(INT,CONVERT(DATETIME,@FictionalDate)) <= 30
May 21, 2013 at 1:28 pm
Edit: Copied wrong data to this post.
May 21, 2013 at 1:37 pm
chris.rogers00 (5/21/2013)
I couldn't see any difference in CPU or Query cost between my answer and Lynn's answer when doing <10 records.It's all down to preference.
DECLARE @FictionalDate DATE = '1969-12-15'DECLARE @bdays TABLE (Birthday CHAR(8))
INSERT @bdays
(Birthday)
VALUES ('20010101'),
('19700102'),
('19700101'),
('19951201'),
('20130521'),
('20130620')
SELECT CONVERT(DATE,Birthday)
FROM @bdays
WHERECONVERT(INT,CONVERT(DATETIME,Birthday)) - CONVERT(INT,CONVERT(DATETIME,@FictionalDate)) <= 30
Actually, your code doesn't work. Run the following:
DECLARE @FictionalDate DATE = getdate() --'1969-12-15'
DECLARE @bdays TABLE (Birthday CHAR(8))
INSERT @bdays
(Birthday)
VALUES ('20010101'),
('19700102'),
('19700101'),
('19951201'),
('20130521'),
('20130620')
SELECT CONVERT(DATE,Birthday)
FROM @bdays
WHERECONVERT(INT,CONVERT(DATETIME,Birthday)) - CONVERT(INT,CONVERT(DATETIME,@FictionalDate)) <= 30
Here are the results I get:
(No column name)
2001-01-01
1970-01-02
1970-01-01
1995-12-01
2013-05-21
2013-06-20
Remember, the requirement was to show who was having a birthday within the next 30 days.
May 22, 2013 at 2:44 am
Yeah, my bad.. Should be BETWEEN 0 AND 30
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply