Querying on Datetime from ASP page

Posted on 2011-05-11
Last Modified: 2012-05-11

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.


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

Question by:SasDev
    LVL 28

    Expert Comment


    Where is strSdate coming from?

    LVL 1

    Author Comment

    strSdate is being grabbed from the asp page and is the value entered by the user.
    LVL 28

    Accepted Solution

    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 )      & "

    LVL 5

    Assisted Solution

    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

    LVL 28

    Expert Comment

    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 &"
    LVL 10

    Assisted Solution

    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,

    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

    LVL 1

    Author Comment


    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.
    LVL 10

    Expert Comment

    Glad you got it sorted.

    thanks for the points :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    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…
    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    760 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

    11 Experts available now in Live!

    Get 1:1 Help Now