Solved

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

Posted on 2004-05-02
5
16,575 Views
Last Modified: 2011-08-18
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
Comment
Question by:Pietro_P
5 Comments
 
LVL 6

Expert Comment

by:pkaleda
Comment Utility
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
 

Author Comment

by:Pietro_P
Comment Utility
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
 
LVL 12

Accepted Solution

by:
rcmb earned 20 total points
Comment Utility
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
 
LVL 6

Expert Comment

by:pkaleda
Comment Utility
I was just about to suggest your second method RCMB.  Access does not support convert.
0
 
LVL 6

Expert Comment

by:Lord_McFly
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now