Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

asp syntax - sql query

Here is the code we have been using for months, and it has worked great.  Now it's not working.  The only difference i can see is the month has gone to a two digit number (from 9 to 10).

It basically pulls all closed tickets that are in the past two months.  So it looks at date2 (date ticket closed in database) and grabs everything that is greater than the current month minus one.  So right now we should be getting all closed tickets in September (9) and October (10), but we are only getting Septembers closed tickets.

conn.open "SELECT * FROM helpdesk WHERE Status = 'Closed' and date2 > '" & month(date) -1 & "-%" & year(date()) & "%' ORDER BY ID DESC;", "DSN=helpdesk"

This is the way the date looks in the database:
10-11-2007     13:50:40
9-28-2007     10:22:23

Ideas as to why this isn't working now?
0
bschwarting
Asked:
bschwarting
  • 14
  • 13
2 Solutions
 
L00MCommented:
What kind of database is this?
Assuming it's SQL Server, have you thought about using the DateDiff function instead?

http://msdn2.microsoft.com/en-us/library/aa258269(sql.80).aspx
0
 
bschwartingAuthor Commented:
it's an access database, sorry.
0
 
SQL_SERVER_DBACommented:
month(date2)>
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bschwartingAuthor Commented:
SQL_SERVER_DBA,

not sure that will work since i have the date/time both in the same field, see data above
0
 
L00MCommented:
DateDiff will work. Note it will allow you to specify down to the millisecond using 'ms'.

@SQL SERVER DBA:
Huh?
month(date2)>
And?

date2 is a column name, not an asp variable.
0
 
bschwartingAuthor Commented:
L00M, could you provide an example with my info above?
0
 
L00MCommented:
   today = Date()
 
    thisMonth = month(today)
    thisDay = day(today)
    refd = today + 1 - thisDay
    fdlm = FormatDateTime(dateadd("m", -1, refd),2)

conn.open "SELECT id, Status, date2 FROM helpdesk WHERE (Status = 'Closed') AND (DATEDIFF(dd, '" & fdlm & "', date2) > 0) ORDER BY id DESC",  "DSN=helpdesk"

I believe that should do it.
0
 
bschwartingAuthor Commented:
L00M,

that didn't work.

it looks like if my month were 09 instead of 9 it would work.

any idea how i can add a zero in front of my 9 in my select query?

This works in my output, but how would i incorporate that in my query?
<% response.write "0" & month(date) -1 %>
0
 
L00MCommented:
Just define it before hand like:

Dim myMonth
If Month(date) < 10 then myMonth = "0" & Month(date) Else myMonth = Month(date)

Then just use myMonth in your query.
0
 
L00MCommented:
oops, forgot to subtract 1

If Month(date) < 11 then myMonth = "0" & (Month(date)-1) Else myMonth = (Month(date)-1)
0
 
L00MCommented:
However, I still believe in using the DateDiff function. You're going to have a problem in January. 1-1= 0.
0
 
bschwartingAuthor Commented:
yeah, i see what your saying.  january wouldn't work either.

but, will the DateDiff work the way i have my data?  i store the date/time in the same field.
0
 
L00MCommented:
Yes, it absolutely will. I set up a sample table here with a datetime field and tested it. I recommend you go in to Query Analyzer and punch in:

SELECT id, Status, date2, DATEDIFF(dd, '9/1/2007', date2) as DD FROM helpdesk WHERE (Status = 'Closed') ORDER BY id DESC

Then you'll see what DATEDIFF produces and can tweak your query from there.
0
 
bschwartingAuthor Commented:
ok, i'm using access "SQL view"

when i run it, i get this error:

"circular reference caused by alias 'DD' in query definition's select list
0
 
L00MCommented:
change DD to 'BLAH' or whatever
0
 
bschwartingAuthor Commented:
ok, that worked, and i figured out the access format.

SELECT id, Status, date2, DATEDIFF("d",'9/1/2007',date2) AS testoutput FROM helpdesk WHERE (Status='Closed') ORDER BY id DESC;

Now, i'm trying to convert that to the ASP page with this code:

conn.open "SELECT * FROM helpdesk WHERE Status = 'closed' and (DATEDIFF(d, '10/12/2007', date2) < 61) ORDER BY ID DESC;", "DSN=helpdesk"

and i get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
/updated/cgi-bin/helpdeskqueueupdate.closed.asp, line 27

ideas?
0
 
L00MCommented:
Paste the new SQL statement into SQL View... do you get the same error?

SELECT * FROM helpdesk WHERE Status = 'closed' and (DATEDIFF(d, '10/12/2007', date2) < 61) ORDER BY ID DESC

0
 
bschwartingAuthor Commented:
if i run it just like that in access it prompts "Enter Parameter Value" for d
0
 
bschwartingAuthor Commented:
if i run it like this:

SELECT * FROM helpdesk WHERE Status = 'closed' and (DateDiff('d', '10/12/2007', date2) < 61) ORDER BY ID DESC

i get all records in the database, like it's not doing the DateDiff function.
0
 
L00MCommented:
Ok, the parameter problem is what was causing the initial problem. So... let's make sure the DateDiff is functioning as desired. Change SQL to:

SELECT *, DateDiff('d', '10/12/2007', date2) AS testouput FROM helpdesk WHERE Status = 'closed' and (DateDiff('d', '10/12/2007', date2) < 61) ORDER BY ID DESC

testoutput SHOULD all be less than 61
0
 
L00MCommented:
And, I believe you have the date variables mixed up. I think you want:

SELECT     id, Status, date2, DATEDIFF(dd, date2, '10/12/2007') AS testouput
FROM         helpdesk
WHERE     (Status = 'closed') AND (DATEDIFF(dd, date2, '10/12/2007') < 61)
ORDER BY id DESC

Quote the 'dd' or change to 'd' as needed.
0
 
bschwartingAuthor Commented:
ok, the second one worked great in access.  pulled exactly what i needed.

SELECT id, Status, date2, DATEDIFF("d", date2, '10/12/2007') AS testouput FROM         helpdesk WHERE     (Status = 'closed') AND (DATEDIFF("d", date2, '10/12/2007') < 61) ORDER BY id DESC

now i just need to get it into what ASP wants to see.

when i copy/paste that exact query to the ASP page it yells at the "d" (expected end of statement)

when i remove the quotes, it then gives this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
/updated/cgi-bin/helpdeskqueueupdate.closed.asp, line 27

ideas?
0
 
bschwartingAuthor Commented:
does this matter? "AS testouput"

because testoutput doesn't exist in my access database.
0
 
L00MCommented:
No, the 'testoutput' wont matter - it's an alias... it is the column name. It'll just be returned as a column in the dataset.

change the double quotes around "d" to single quotes 'd'

0
 
bschwartingAuthor Commented:
that worked!  included a few more column names, and my code is happy!  thanks for all the work L00M, i wish i had more points to give!
0
 
L00MCommented:
Glad to help!
Good luck with  your endeavours!
0
 
bschwartingAuthor Commented:
now that i have that working good, i've added some cool stuff!  (cool to me)

if Request.Querystring("daysback") = "" then daysback = 5 else daysback = Request.Querystring("daysback")

Closed Tickets - Go back <a href="helpdeskqueueupdate.closed.asp?daysback=15"><font color="red">15</font></a>, <a href="helpdeskqueueupdate.closed.asp?daysback=30"><font color="red">30</font></a>, <a href="helpdeskqueueupdate.closed.asp?daysback=45"><font color="red">45</font></a>, <a href="helpdeskqueueupdate.closed.asp?daysback=60"><font color="red">60</font></a>

so it looks like this:
Closed Tickets - Go back 15, 30, 45, 60 days (with hyperlinks on each number)

now we can choose how far we want to go back.
0
 
L00MCommented:
Very nice! See... DateDiff is the way to Go! You could even put that in a drop down from 1 to 12 months or whatever. :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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