Solved

Correct ASP Syntax for Date()-365

Posted on 2003-11-24
48
487 Views
Last Modified: 2009-12-16

I want to show the order results from the last year. Therefore I used this:

sql = sql & " AND ((tblOrder.dtmInput)>'" & Date()-365"'))"

The problem is the Date()-365. Does this work in sql-asp?
Thanks to inform me when you know the exact syntax?

Regards,
Stevius
0
Comment
Question by:Stevius
  • 15
  • 13
  • 12
  • +2
48 Comments
 
LVL 8

Expert Comment

by:xabi
ID: 9811084
Try this:

sql = sql & " AND ((tblOrder.dtmInput)>'" & sysdate-365"'))"

xabi
0
 
LVL 8

Expert Comment

by:xabi
ID: 9811123
or try this other

sql = sql & " AND ((tblOrder.dtmInput)>'" & getDate()-365"'))"

xabi
0
 

Author Comment

by:Stevius
ID: 9811181

When I try this I get expect end of statement error.
0
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9811182
Use the DateAdd() method:

DateAdd("d",-365,Date())

FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9811190
sql = sql & " AND ((tblOrder.dtmInput)>'" &  DateAdd("d",-365,Date() &  "'))"

FtB
0
 

Author Comment

by:Stevius
ID: 9811238
Thanks for the help: The error now is Expected ')'
Therefore I have copied the complete sql statement.
Everything works fine when I exclude the date rule.

sql = "SELECT tblOrder.IDOrder, tblBook.IDBook, tblBook.strTitle, tblEmployee.strLastName, tblEmployee.usercode"
sql = sql & " , tblBook.blnCheckIn, tblOrder.dtmInput"
sql = sql & " FROM tblEmployee INNER JOIN (tblOrder LEFT JOIN tblBook ON tblOrder.IDOrder = tblBook.IDOrder)"
sql = sql & " ON tblEmployee.IDEmployee = tblOrder.IDEmployee"
sql = sql & " WHERE (((tblBook.blnCheckIn)=No) AND ((tblEmployee.usercode)='" & userid(1) & "')"
sql = sql & " AND ((tblOrder.dtmInput)>'" & DateAdd("d",-365,Date() & "'))"
sql = sql & " ORDER BY tblOrder.IDOrder;"
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 9811239
I would use Datediff function

sql = sql & " datediff(yy,tblOrder.dtmInput,getdate()) = 1"
0
 
LVL 8

Expert Comment

by:xabi
ID: 9811259
sql = "SELECT tblOrder.IDOrder, tblBook.IDBook, tblBook.strTitle, tblEmployee.strLastName, tblEmployee.usercode"
sql = sql & " , tblBook.blnCheckIn, tblOrder.dtmInput"
sql = sql & " FROM tblEmployee INNER JOIN (tblOrder LEFT JOIN tblBook ON tblOrder.IDOrder = tblBook.IDOrder)"
sql = sql & " ON tblEmployee.IDEmployee = tblOrder.IDEmployee"
sql = sql & " WHERE (((tblBook.blnCheckIn)=No) AND ((tblEmployee.usercode)='" & userid(1) & "')"
sql = sql & " AND tblOrder.dtmInput > (SysDate-30)"
sql = sql & " ORDER BY tblOrder.IDOrder;"
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9811264
What happens if you do this:

response.write sql
response.end


so that we can see the output.


FtB
0
 
LVL 8

Expert Comment

by:xabi
ID: 9811266
sql = sql & " AND tblOrder.dtmInput > (SysDate-365)"   ;)
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9811280
sql = sql & " AND ((tblOrder.dtmInput)>'" & DateAdd("d",-365,Date()) & "')"

FtB
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9811285
Inorder to use the sql date format, use..

sql = sql & " AND ((tblOrder.dtmInput)> (SELECT DATEADD(day, -365, (select getDate()))))"

Cheers!!
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9811290
The issue was that you needed to close the parentheses for the DateAdd() method but it was included inside the quotation marks.

FtB
0
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 250 total points
ID: 9811296
So, this should do it:

sql = "SELECT tblOrder.IDOrder, tblBook.IDBook, tblBook.strTitle, tblEmployee.strLastName, tblEmployee.usercode"
sql = sql & " , tblBook.blnCheckIn, tblOrder.dtmInput"
sql = sql & " FROM tblEmployee INNER JOIN (tblOrder LEFT JOIN tblBook ON tblOrder.IDOrder = tblBook.IDOrder)"
sql = sql & " ON tblEmployee.IDEmployee = tblOrder.IDEmployee"
sql = sql & " WHERE (((tblBook.blnCheckIn)=No) AND ((tblEmployee.usercode)='" & userid(1) & "')"
sql = sql & " AND ((tblOrder.dtmInput)>'" & DateAdd("d",-365,Date()) & "')"
sql = sql & " ORDER BY tblOrder.IDOrder;"
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9811303
I think jitganguly's suggestion is the most optimised one..

Cheers!!
0
 

Author Comment

by:Stevius
ID: 9811346
Thanks You All !!! I used the solution of Fritz. Easy to copy and it works !!! Yeah !
I didn't had to deal with the parentheses and all the other signs that confuses me . TC, Stevius
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9811368
Glad to have helped,

FtB
0
 

Author Comment

by:Stevius
ID: 9811382
And the db is not excessive nor immense
Thx Ftb
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9811398
I still stand by my statement ... ;0)

>>I think jitganguly's suggestion is the most optimised one..<<

Cheers!!
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9811442
If the DBMS supports the DateDiff() method, then that is a good way to do it (SQL Server and Access support this I believe). The reason that I went with the DateAdd is so that it could happen outside of the SQL, keeping it compatible with a wider range of DBMS's.

FtB

0
 

Author Comment

by:Stevius
ID: 9811444
:) Anyway I'll keep all solutions.
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 9811513
>>The reason that I went with the DateAdd is so that it could happen outside of the SQL, keeping it compatible with a wider range of DBMS's

1.More overheads with this solution.
2. You don't write application for different types of DBMS's but cross browser

Anyway, No offense Patrick.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9811579
Jit--

Not to be contrary, but I think that you do have to keep in mind the DBMS when writing your SQL. For example, you can include a number of different functions in a SQL statement created for Access that will fail with MySQL. Date() works fine in the former but fails in the latter.

So what I am saying here is that if you keep your functions so that they are executed in the VBScript rather than in the SQL, then you will have a wider compatibility with different DBMS's. However, if you know what DBMS you are going to use and are familiar with what methods are supported, then you may have a performace gain for using the methods within the SQL. Does that make sense?

FtB
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9811948
Gee.. did i start off a war here.. LOL

Anyway, Fritz has a valid reason i guess.. but how ever.. i still believe that if application portability is not an issue, then performance should get top prority. Anyway.. In most cases, how ever well written the code is, we would have to do some re coding when porting the app. Also, what if the application portability is to happen the other way round ie, SQL/Access stays and the app is to be ported to PHP/.NET/JSP??

Anyway.. No application is 100% portable.. so i try and code for performance most of the time.. unless there is a clear need for portability and is identified during the project scoping.

Cheers!!
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9812163
Well--you are absolutely correct about that. If portablility is not an issue, and the DBMS supports the function, then it is best to write for performance.

I still don't know what DBMS is being used, so I tried to code in the most generic way. If truth be told, however, I am guessing that most likely the answer is Access or SQL Server, in which case DateDiff() should be supported in the native SQL.

FtB

0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9812468
Agree to that.. ;o)

Cheers!!
0
 

Author Comment

by:Stevius
ID: 9816538
I had to leave urgently yesterday.
If anyone still interested the db used is Access and portability is not important for this one.  I agree to go for performance and am trying to make Datediff work.
Regards for all the practical info.
Stevius
0
 

Author Comment

by:Stevius
ID: 9817111
Apparently today it doesn't seem to work. Anybody can help?

***Error Type:***
Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.

***CODE USED***
sql = "SELECT tblOrder.IDOrder, tblBook.IDBook, tblBook.strTitle, tblEmployee.strLastName, tblEmployee.usercode"
sql = sql & " , tblBook.blnCheckIn, tblOrder.dtmInput"
sql = sql & " FROM tblEmployee INNER JOIN (tblOrder LEFT JOIN tblBook ON tblOrder.IDOrder = tblBook.IDOrder)"
sql = sql & " ON tblEmployee.IDEmployee = tblOrder.IDEmployee"
sql = sql & " WHERE (((tblBook.blnCheckIn)=No) AND ((tblEmployee.usercode)='" & userid(1) & "')"
sql = sql & " AND ((tblOrder.dtmInput)>'" & DateAdd("d",-365,Date()) & "'))"
sql = sql & " ORDER BY tblOrder.IDOrder;"

Thanks,
Stevius
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9817811
Do a response.write on the sql string and port your code here...

Cheers!!
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9818896
Give this a shot--it uses the DateDiff() method discussed above:

sql = "SELECT tblOrder.IDOrder, tblBook.IDBook, tblBook.strTitle, tblEmployee.strLastName, tblEmployee.usercode"
sql = sql & " , tblBook.blnCheckIn, tblOrder.dtmInput"
sql = sql & " FROM tblEmployee INNER JOIN (tblOrder LEFT JOIN tblBook ON tblOrder.IDOrder = tblBook.IDOrder)"
sql = sql & " ON tblEmployee.IDEmployee = tblOrder.IDEmployee"
sql = sql & " WHERE (((tblBook.blnCheckIn)=No) AND ((tblEmployee.usercode)='" & userid(1) & "')"
sql = sql & " datediff(yy,tblOrder.dtmInput,getdate()) = 1"
sql = sql & " ORDER BY tblOrder.IDOrder;"


FtB
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 9818927
Patrick is missing an and before datediff

sql = "SELECT tblOrder.IDOrder, tblBook.IDBook, tblBook.strTitle, tblEmployee.strLastName, tblEmployee.usercode"
sql = sql & " , tblBook.blnCheckIn, tblOrder.dtmInput"
sql = sql & " FROM tblEmployee INNER JOIN (tblOrder LEFT JOIN tblBook ON tblOrder.IDOrder = tblBook.IDOrder)"
sql = sql & " ON tblEmployee.IDEmployee = tblOrder.IDEmployee"
sql = sql & " WHERE (((tblBook.blnCheckIn)=No) AND ((tblEmployee.usercode)='" & userid(1) & "')"
sql = sql & "  and datediff(yy,tblOrder.dtmInput,getdate()) = 1"
sql = sql & " ORDER BY tblOrder.IDOrder;"


Do I get points ? :-)
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9819013
Good catch!

FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 9819018
However, I copied it from your post above!

FtB
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 9819024
However I didn't put the whole SQL above on my first post  :-), so no and business
0
 

Author Comment

by:Stevius
ID: 9823232
After Sale Service :)

This is the sql string returned

SELECT tblOrder.IDOrder, tblBook.IDBook, tblBook.strTitle, tblEmployee.strLastName, tblEmployee.usercode , tblBook.blnCheckIn, tblOrder.dtmInput FROM tblEmployee INNER JOIN (tblOrder LEFT JOIN tblBook ON tblOrder.IDOrder = tblBook.IDOrder) ON tblEmployee.IDEmployee = tblOrder.IDEmployee WHERE (((tblBook.blnCheckIn)=No) AND ((tblEmployee.usercode)='GGB105') and datediff(yy,tblOrder.dtmInput,getdate()) = 1 ORDER BY tblOrder.IDOrder;

When I execute the sql I get an error message.

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression '(((tblBook.blnCheckIn)=No) AND ((tblEmployee.usercode)='GGB105') and datediff(yy,tblOrder.dtmInput,getdate()) = 1 ORDER BY tblOrder.IDOrder;'.
/intern/sb/bib_myLecture.asp, line 153

Line 153 is : rs4.open sql, cn

Thanks!
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9823433
I think you have an extra "("

Try..

sql = "SELECT tblOrder.IDOrder, tblBook.IDBook, tblBook.strTitle, tblEmployee.strLastName, tblEmployee.usercode"
sql = sql & " , tblBook.blnCheckIn, tblOrder.dtmInput"
sql = sql & " FROM tblEmployee INNER JOIN (tblOrder LEFT JOIN tblBook ON tblOrder.IDOrder = tblBook.IDOrder)"
sql = sql & " ON tblEmployee.IDEmployee = tblOrder.IDEmployee"
sql = sql & " WHERE ((tblBook.blnCheckIn)=No) AND ((tblEmployee.usercode)='" & userid(1) & "')"
sql = sql & "  and datediff(yy,tblOrder.dtmInput,getdate()) = 1"
sql = sql & " ORDER BY tblOrder.IDOrder;"

Cheers!!
0
 

Author Comment

by:Stevius
ID: 9836512

HAd to do something else then asp.
strange but true: Undefined function 'getdate' in expression.
Anyone knows what to do?
Thanks ;)
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9836827
Try..

Try..

sql = "SELECT tblOrder.IDOrder, tblBook.IDBook, tblBook.strTitle, tblEmployee.strLastName, tblEmployee.usercode"
sql = sql & " , tblBook.blnCheckIn, tblOrder.dtmInput"
sql = sql & " FROM tblEmployee INNER JOIN (tblOrder LEFT JOIN tblBook ON tblOrder.IDOrder = tblBook.IDOrder)"
sql = sql & " ON tblEmployee.IDEmployee = tblOrder.IDEmployee"
sql = sql & " WHERE ((tblBook.blnCheckIn)=No) AND ((tblEmployee.usercode)='" & userid(1) & "')"
sql = sql & "  and datediff(yy,tblOrder.dtmInput,(select getdate())) = 1"
sql = sql & " ORDER BY tblOrder.IDOrder;"

Cheers!!
0
 

Author Comment

by:Stevius
ID: 9836891
! Thanks ap_sajith !

I get the following : Syntax error. in query expression '((tblBook.blnCheckIn)=No) AND ((tblEmployee.usercode)='GGB105') and datediff(yy,tblOrder.dtmInput,(select getdate())) = 1'.

When I execute the sql I get this :
SELECT tblOrder.IDOrder, tblBook.IDBook, tblBook.strTitle, tblEmployee.strLastName, tblEmployee.usercode , tblBook.blnCheckIn, tblOrder.dtmInput FROM tblEmployee INNER JOIN (tblOrder LEFT JOIN tblBook ON tblOrder.IDOrder = tblBook.IDOrder) ON tblEmployee.IDEmployee = tblOrder.IDEmployee WHERE ((tblBook.blnCheckIn)=No) AND ((tblEmployee.usercode)='GGB105') and datediff(yy,tblOrder.dtmInput,(select getdate())) = 1 ORDER BY tblOrder.IDOrder;

Hope this helps ...
0
 

Author Comment

by:Stevius
ID: 9836917
When I try Date() I get the message that no value is given for one or more parameters
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9837245
See if this query returns any error...

SELECT tblOrder.IDOrder, tblBook.IDBook, tblBook.strTitle, tblEmployee.strLastName, tblEmployee.usercode , tblBook.blnCheckIn, tblOrder.dtmInput FROM tblEmployee INNER JOIN (tblOrder LEFT JOIN tblBook ON tblOrder.IDOrder = tblBook.IDOrder) ON tblEmployee.IDEmployee = tblOrder.IDEmployee WHERE datediff(yy,tblOrder.dtmInput,(select getdate())) = 1 ORDER BY tblOrder.IDOrder;


Cheers!!
0
 

Author Comment

by:Stevius
ID: 9837277
I get a syntax error when executed in access sql query.

It works when I use
SELECT tblOrder.IDOrder, tblBook.IDBook, tblBook.strTitle, tblEmployee.strLastName, tblEmployee.usercode , tblBook.blnCheckIn, tblOrder.dtmInput FROM tblEmployee INNER JOIN (tblOrder LEFT JOIN tblBook ON tblOrder.IDOrder = tblBook.IDOrder) ON tblEmployee.IDEmployee = tblOrder.IDEmployee WHERE datediff(yy,tblOrder.dtmInput,(date())) = 1 ORDER BY tblOrder.IDOrder;
>The only problem is that he asks for a parameter "yy"

I tried to response.write getDate > Type mismatch: 'getDate'
response.write date() returns the current date.

When I remove the sql line with datediff the query runs.

0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9837299
getDate is a SQL function.. not an asp function... it is equivalent to the now function is vbscript..

BTW.. is your db access or SQL?
Cheers!!
0
 

Author Comment

by:Stevius
ID: 9837341
Hey ap_sajith,

Finally a solution.
sql = sql & " AND ((tblOrder.dtmInput)>" & "#" & DateAdd("d",-365,Date()) & "#" & ")". Apparently with dates we need the #-sign.

Thanks !
0
 

Author Comment

by:Stevius
ID: 9837389
So can only be used within sql
DB still is Access  -  I'm thinking about transfer to SQL.


0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9837417
For access db.. yes... you need to wrap the dates in "#".. how ever its not needed in SQL server.

Cheers!!
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9837467
you might have modify your queries a bit when u migrate the app to sql.... BTW.. Date Issues are the major head aches for Asp developers ;o)


Cheers!!
0
 

Author Comment

by:Stevius
ID: 9837657
Now I know where to find already one problem. Remove the #'s.
Ai and I still have some queries with dates to go  ...
Your tip in executing the statement in access really helped a lot.
SY, Stevius
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Using querystring in a hyperlink 3 56
ASP Focus problem 3 68
RegEx Help - open to other simple suggestions 8 58
MS SQL and column defined as time 7 12
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
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/…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

830 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