Anniversary List for employee's

I found this code off the web and I can get it to work to a point the problem is its not showing all the employees aniversarys for this month I have the dates in a column named StartDate and they are formated 10/31/2006
<%
Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

conn.ConnectionString = "DSN=intranet"
conn.Open
nowMon = Month(now)
nowDay = Day(now)
nowDOH = nowMon & "/" & nowDay


SQLcmd = "Select * From employeeinfo where StartDate Like '%" & nowDOH & "%' order by StartDate"
'response.write sqlcmd
Set rs = conn.Execute(SQLcmd)             
Do While Not rs.EOF
%> 
<p align="center"><b><i><font size="3" color="#CC0099">Today is <%=rs("FirstName") & " " & rs("LastName")%>'s Aniversary!!!<br>
</font></i></b>      
    
<p></p>          
<%
rs.MoveNext             
Loop
%><div align="center"><center>         
<table border="0" width="100%" cellspacing="0" cellpadding="0">        
<tr><td valign="top"><p align="center"><font size="3">
<font color="#008000">This is Aniversary's list for Month of <%=(MonthName(nowMon))%></font></font>
<div align="center"><center>
<table border="0" cellspacing="5" style="border: 2 dotted #FF0000">
<%SQLcmd = "Select * From employeeinfo Where StartDate Like '%"& nowDOH & "%' order by StartDate"
Set rs = conn.Execute(SQLcmd)
Do While Not rs.EOF
     manyday = Day(now) - Clng(day(rs("StartDate")))
	 'manyday =  Clng(day(rs("StartDate"))) - Day(now)

%>                                                                                  
<tr><td align="center"><font color="#CC0099" size="2"><%=rs("FirstName") & " " & rs("LastName")%></font></td>
<td align="center"><font color="#FF0000" size="2"><%=Left(rs("StartDate"),5)%></font></td><%
     If manyday > 0 and manyday <= 7 Then
%><td align="center">
<font color="#996600" size="1">will be celebrate his Aniversary!</font></td><%
     End If
%>
</tr><%                                 
     rs.MoveNext
Loop
%>

Open in new window

JHopkins213Asked:
Who is Participating?
 
Paul MacDonaldDirector, Information SystemsCommented:
Try:
"Select * From employeeinfo where MONTH(StartDate) = MONTH({fn Now()}) order by StartDate"
0
 
JHopkins213Author Commented:
Now it doesnt show any for this month I plcaed this here
<%SQLcmd = "Select * From employeeinfo where MONTH(StartDate) = MONTH({fn Now()}) order by StartDate" 
Set rs = conn.Execute(SQLcmd)
Do While Not rs.EOF
     manyday = Day(now) - Clng(day(rs("StartDate")))
	 'manyday =  Clng(day(rs("StartDate"))) - Day(now)

Open in new window

0
 
Paul MacDonaldDirector, Information SystemsCommented:
Try:
"Select * From employeeinfo where MONTH(StartDate) = MONTH(CONVERT(nvarchar(30), {fn Now()}, 101)) order by StartDate"
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
JHopkins213Author Commented:
Now I'm getting
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[MySQL][ODBC 5.1 Driver][mysqld-5.1.51-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(30), {fn Now()}, 101)) order by StartDate' at line 1

0
 
JHopkins213Author Commented:
Now my dates in mysql are in this format 10/31/2011 and using varchar for type
0
 
Paul MacDonaldDirector, Information SystemsCommented:
Ah, well I'm feeding you SQL Server syntax.  Both queries work great there.  Also, my query returns all records for the entire month.  Yours returns records for the month + day combination.

Rather than go around and around, why not let us know what you do get back from your query if you run it against MySQL directly?  How is what you get back different from what you expect?  Etc...
0
 
JHopkins213Author Commented:
on this one
"Select * From employeeinfo where MONTH(StartDate) = MONTH({fn Now()}) order by StartDate" 

Open in new window

I get Zero results

On this one
Select * From employeeinfo where MONTH(StartDate) = MONTH(CONVERT(nvarchar(30), {fn Now()}, 101)) order by StartDate

Open in new window

I get

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(30), {fn Now()}, 101)) order by StartDate LIMIT 0, 30' at line 1
0
 
JHopkins213Author Commented:
The issue was my fault I didn't have the date table setup corrctly Its working now 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.