Inserting Birthday in Database?

I am creating a very simple table which will consist of Users Birtday (month and day only)
i created a Table
create 2 col.
Username  type varchar
Bday type Datetime

now on ASP side i need to be able to insert the birthday to DB.
so i created 3 dropdownlist
1-User 2-Month 3-Day
all of them are populated .

now here is my problem not sure how i can insert it into DB as a DateTime format ... my goal is i need to be able to insert it and then write a simple SQL to show me all upcoming Birthdays for the next month.

Thank you,
Raul77Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
here you go
 

 

DECLARE @Day tinyint, 
@Month tinyint 
-- for current month 
SELECT UserName, BDay, Bmonth 
FROM Users 
WHERE BDay > @day and Bmonth = @month 
UNION ALL 
SELECT UserName, BDay, Bmonth --- nextMonth 
FROM Users 
WHERE Bmonth = CASE WHEN @month =12 THEN 1 ELSE @month+1 END
UNION ALL 
SELECT UserName, BDay, Bmonth --- nextMonth 
FROM Users 
WHERE Bmonth = CASE WHEN @month =11 THEN 1 
					WHEN @Month =12 THEN 2 
					ELSE @month+2 END

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello Raul77,

if you dont want to put the year, use to differernt columns to store the Month and day, you can query the upcoming birth days by using

SELECT *
FROM urTable
WHERE MONTH (Getdate() ) = dobMonth

Regards,

Aneesh
0
 
Raul77Author Commented:
Hello Aneesh
thanks for the help ,
so i need to create 2 col. in DB , should they be type datetime?

btw i am using MSSQL 2000.

and in case you are wondering this is how i populate my dropdownlist

 DateTimeFormatInfo dtfi = new DateTimeFormatInfo();
        for (int month = 1; month < 13; month++)
        {
            ListItem li = new ListItem();
            li.Text = dtfi.GetMonthName(month);
            li.Value = month.ToString();
            ddlMonth.Items.Add(li);
        }
        for (int i = 1; i < 32; i++)
        {
            ListItem day = new ListItem();
            day.Text = i.ToString();
            day.Value = i.ToString();
            ddlDay.Items.Add(day);
        }

Open in new window

0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Raul77Author Commented:
anyone else has any idea on this?

thanks, i can re-design the table if need be.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
You can use a TinyInt for  these , tiny int can store upto 255
0
 
Raul77Author Commented:
Aneesh:
i have a few problem
1-) i would need 2 months in advanced to show ( previosuly stated 1)
2-) what if todays date is 11/23 2 months in advance with be 11/23 ... how can it know that?
also wouldnt the DAY start playing a role?

if its 11/23 , i dont want to see someones birthday if its on 1/28 ! or if it has passed.

Thanks for the help.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:

DECLARE @Day tinyint,
@Month tinyint
-- for current month
SELECT NAme, dob_Day, dob_Month
FROM urDOBTable
WHERE dob_day > @day and dob_month = @month
UNION ALL
SELECT NAme, dob_Day, dob_Month --- nextMonth
FROM urDOBTable
WHERE dob_month = CASE WHEN @month =12 THEN 1 ELSE @month END
0
 
Raul77Author Commented:
thanks Aneesh,
we are almost there, since i need the next 2 months i modified your script abit however now i am getting 2 table, we need to union ALL selects in 1 table

Thanks,

SELECT username, Bday, Bmonth
FROM users
WHERE Bday > 14 and Bmonth = 5
UNION ALL
SELECT username, Bday, Bmonth --- nextMonth
FROM users
WHERE Bmonth = CASE WHEN 5 =12 THEN 1 ELSE 6 END

SELECT username, Bday, Bmonth
FROM users
WHERE Bday < 14 and Bmonth = 7

SELECT username, Bday, Bmonth
FROM users
WHERE Bday > 14 and Bmonth = 5
UNION ALL
SELECT username, Bday, Bmonth --- nextMonth
FROM users
WHERE Bmonth = CASE WHEN 5 =12 THEN 1 ELSE 6 END
 
SELECT username, Bday, Bmonth
FROM users
WHERE Bday < 14 and Bmonth = 7

Open in new window

0
 
Raul77Author Commented:
i added union and it seem to be working :) thank you Sir.
0
 
Raul77Author Commented:
Thank you,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.