Question Birthday check.

[atr232000]

Hello all

I am new to vb.net but i have been writing my first Application.

The application is a customer database. I have a contacts form and in one of the fields the user puter their date of birth. Alk data is writen to a SQL database.

I also have a listview on the right when the application lauches.
I want this listview to show birthdays that are coming up in say the next 30days.

This is so the user can send a card/ presant.

here is my SQL Update

"update Contacts set contactname='" & tbName.Text & "', PostalAddress='" & tbAddress.Text & "', PhoneNumber='" & tbPhone.Text & "',MobileNumber='" & tbMobile.Text & "', Email='" & tbEmail.Text & "',DateOfBirth='" & Format(dtpDOB.Value, "dd MMM yyyy") & "' where contactID ='" & localContactID & "'"

Then my listview has the select

"SELECT ContactName, DateOFBirth, ContactID FROM Contacts"

I did try
Dim todaysdate As DateTime
todaysdate = Date.Now.AddDays(+30).ToString("dd MMM yyyy")

But it is looking at the YYYY and for it to show evey year the YYYY has to be ignored.

I hope this makes sence can anyone help me please

Thanks

Alan

 

[alliancejhall]

Here's one way...

How about you just strip off the month and the day then add the current year to make a new date?

SELECT ContactName, Convert(DateTime, Month(DateOfBirth) + '/' + Day(DateOfBirth) + '/' + Year(GetDate())) As DateOfBirth, ContactID FROM Contacts

may not be the best way to do this but it's one way.

 

[atr232000]

Hi

Thanks for your comments but I get a return code error.

"Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '/' to data type int."

I really am stuck with how to accomplish this.

Any help would be great

 

[alliancejhall]

Sorry that was my fault... I wasn't thinking about what I was typing haha.

This way may be even easier.:

SELECT ContactName, DateAdd(yyyy,Year(GetDate()) - Year(DateOfBirth), DateOfBirth) As DateOfBirth, ContactID FROM Contacts

Basically I'm subtracting the birth year from the current year then adding the difference to the birth year to bring the birth year to the current year. Does that make sense?

Basically
DateOfBirth = '4/7/2000'
GetDate() = '1/10/2010'
2010-2000 = 10
4/7/2000 + 10 years = 4/7/2010

 

[atr232000]

Thanks.


If i use your SQL and then I want to add the below |I still do not get a return that lists the birthdays due in the next 30 days.

Dim todaysdate As DateTime
todaysdate = Date.Now.AddDays(+30).ToString("dd MMM yyyy")

Try
Dim theBirthdaysearch As String = "SELECT ContactName, DateAdd(yyyy,Year(GetDate()) - Year(DateOfBirth), DateOfBirth) As DateOfBirth, ContactID FROM Contacts where dateofbirth <= '" & todaysdate & "'"

popLV(lvBirth, theBirthdaysearch, False)

Catch ex As Exception
MsgBox(RoutineName & vbCrLf & ex.Message)
End Try

Any more Ideas

Thanks Again

 

[alliancejhall]

Why not...

Why are you using a variable for today's date? You can just get the date in the query, but that's not your issue. The issue is because you are SELECTING that birthdate when that should be your WHERE clause. I was giving you an example of how to do it so you could see what I was talking about.

What you want is:

Dim theBirthdaysearch As String = "SELECT ContactName,  DateOfBirth, ContactID FROM Contacts where DateAdd(yyyy,Year(GetDate()) - Year(DateOfBirth), DateOfBirth) <=  Convert(Varchar, GetDate(),101)"

But this will only show you birthdays that come before today's date. I'm assuming you don't want that. You want birthdays between today's date and 30 days from now correct?

Dim theBirthdaysearch As String = "SELECT ContactName,  DateOfBirth, ContactID FROM Contacts where DateAdd(yyyy,Year(GetDate()) - Year(DateOfBirth), DateOfBirth) Between  DateAdd(d,-1,Convert(Varchar, GetDate(),101) And DateAdd(d,30,Convert(Varchar, GetDate(),101)"

What this statement is doing is Selecting all Contacts whose birthday falls between yesterday and 30 days from today

So Today = '1/10/2010'
Get any birthday between 1/09/2010 and 2/09/2010

Hopefully this makes sense.

 

[atr232000]

Hi alliancejhall

Sorry I have not replied sooner.

I have been busy but I really appriciate your time.

That worked a treat

thanks again

Alan