Learn how to a build a cloud-first strategyRegister Now

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

Querying on Datetime from ASP page

Hello,

I'm trying to query a table on a datetime field from my classic ASP page and it wouldn't fetch anything. When I run the same query through SQL management studio I get results. I believe the problem is with changing the date type and sending the values from the page?

Sample data from table myTable:

ID            empnum   sdate                                       sid     comments
40098      1234      2011-05-19 00:00:00.000      22        Test
40097      1234      2011-02-14 00:00:00.000      24        Approved
40096      1234      2011-02-14 00:00:00.000      17        Approved
40091      1234      2011-02-16 00:00:00.000      17      
40090      4567      2011-02-15 00:00:00.000      17      


I'd appreciate any help.

Thanks,


PS: strSdate is being grabbed from the asp page and is the value entered by the user.


daypart = datepart("d", strSdate)
	monthpart = datepart("m", strSdate)
	yearpart = datepart("yyyy", strSdate)
		
		selector = "select count(*) as dCount from myTable where empnum = '"& strEnum &"' and ((datepart(d, sdate) = '"& daypart &"') and (datepart(m, sdate) = '"& monthpart &"')) "
		set recordS = conn1.execute(selector)
			dateCount = recordS("dCount")
		set recordS = Nothing

Open in new window

0
SasDev
Asked:
SasDev
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
sammySeltzerCommented:


Where is strSdate coming from?

0
 
SasDevAuthor Commented:
strSdate is being grabbed from the asp page and is the value entered by the user.
0
 
sammySeltzerCommented:
Well, in that case, I normally use a function:

Function GetDBDate( fld )
    Dim dt
    dt = CDate(0)
    On Error Resume Next
        dt = CDate(fld.value)
    On Error GoTo 0
    GetDBDate = dt
End Function

Then on your code, you would do something like this:
strSdate = request("userPassedDate")
            selector = "select count(*) as dCount from myTable where empnum = '"& strEnum &"' and yourdateField = "  &      GetDate(strSdate )      & "


0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
bitrefCommented:
Try to remove the single quotes:
selector = "select count(*) as dCount from myTable where empnum = '"& strEnum &"' and ((datepart(d, sdate) = "& daypart &") and (datepart(m, sdate) = "& monthpart &")) "
		

Open in new window

0
 
sammySeltzerCommented:
No, you need the single quotes on dates *unless* the date is a number or the single quotes are encased somewhere as my function suggests.

But you raise a good point about single quotes.

What datatype is empnum?

If it is an integer, then remove the single quotes and just have "& strEnum &"
0
 
OnALearningCurveCommented:
Hi SasDev,

try replacing your "selector" with the attached example.

As with the comment from sammySeltzer above, if empnum is a numeric value you will need to take out the single quotes surrounding it.

Hope this helps,

Mark.
selector = "SELECT COUNT(1) AS dCount FROM myTable WHERE empnum = '"& strEnum &"' AND  DAY(sdate) = "& daypart &" AND MONTH(sdate) = "& monthpart &" AND YEAR(sdate) = "& yearpart

Open in new window

0
 
SasDevAuthor Commented:
Okay,

I'm going to sound stupid but I admit that it was a silly mistake which resulted in hours and hours of frustration :) When I checked my code again this morning I realized that I was using a wrong variable for empnum. I was supposed to be querying: empnum = '"& strEmpNum &"' in the WHERE clause instead of empnum = '"& strEnum &"'.

Although, comments from you guys did help.

Thanks much.
0
 
OnALearningCurveCommented:
Glad you got it sorted.

thanks for the points :)
0

Featured Post

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!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now