Go Premium for a chance to win a PS4. Enter to Win

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

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
0
Pietro_P
Asked:
Pietro_P
1 Solution
 
pkaledaCommented:
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())))"
0
 
Pietro_PAuthor Commented:
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!!!!!!!!
0
 
rcmbCommented:
Two things to try:

Instead of getdate try Date() AND Date()+7

Or if your are doing this in ASP why not store the values in a variable and just call the variable
<%
CurrDate = Date
NextWeekDate = Date +7
%>
Where GameDate between #" & CurrDate & "# and #" & NextWeekDate & "#

RCMB
0
 
pkaledaCommented:
I was just about to suggest your second method RCMB.  Access does not support convert.
0
 
Lord_McFlyCommented:
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
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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