Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

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

0
JHopkins213
Asked:
JHopkins213
  • 5
  • 3
1 Solution
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now