asked on

Convert GetDate() to (DD/MM/YYYY) format

I am trying to run a conditional SQL statement to append info from one table to another where the Date in the DateField is >Todays but <Today+7.

I am using getdate() but i keep getting this error:

Undefined function 'getdate' in expression.
/DIGFootball/Admin/FriDiv3/update2.asp, line 143

[Line 142 =] sql6 = "Insert Into tblWeeklyFixtures_Div3 Select * From tblAllFixtures_div3 Where GameDate between getdate() and getdate()+7"
[Line 143 =] conn.execute(sql6)

I take it this error occurs because i need to convert the date format of Getdate() to (dd/mm/yyyy) but i dont know how to do this. does anyone know how to do this and where i would need to put it?

Thanks for taking the time to read this

pkaleda

Try this:

[Line 142 =] sql6 = "Insert Into tblWeeklyFixtures_Div3 Select * From tblAllFixtures_div3 Where GameDate between convert(DateTime,convert(varchar(11),getdate()))) and dateadd(day,+7 ,convert(DateTime,convert(varchar(11),getdate())))"

Pietro_P

ASKER

Thanks for the reply.

I tried it but i got this error

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'convert' in expression.

I just cant seem to please this god damn ASP!!!!!!!!

ASKER CERTIFIED SOLUTION

rcmb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pkaleda

I was just about to suggest your second method RCMB. Access does not support convert.

Lord_McFly

Try the following...

sql6 = "INSERT INTO tblWeeklyFixtures_Div3 " &_
"SELECT * " &_
"FROM tblAllFixtures_div3 " &_
"WHERE DATEDIFF('d','" & Date() & "',GameDate) >= 0 AND " &_
"DATEDIFF('d','" & DATEADD("d",7,Date()) & "',GameDate) <= 0"

Doing the following test...

<%@ Language=VBScript %>
<%
Dim sDate(4)

sDate(0) = "01/05/2002"
sDate(1) = "01/06/2004"
sDate(2) = "02/05/2004"
sDate(3) = "01/09/2004"
sDate(4) = "07/05/2004"

For iLp = 0 to UBOund(sDate)
      If DATEDIFF("d",Date(),sDate(iLp)) >= 0 and DATEDIFF("d",DATEADD("d",7,Date()),sDate(iLp)) <= 0 then
            Response.Write sDate(iLp) & "<br>"
      End If
Next
%>

Result....

02/05/2004
07/05/2004