• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 16663
  • 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
1 Solution
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_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!!!!!!!!
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 & "#

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


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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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