SQL Select ......... BETWEEN two Dates

Results 1 to 5 of 5

Thread: SQL Select ......... BETWEEN two Dates

  1. #1
    Join Date
    Dec 1969
    Posts
    62

    Default SQL Select ......... BETWEEN two Dates

    I am trying to run a Select Statement and display only records that match the criteria AND BETWEEN 2 date values entered by the user. The problem I am having is that my field datatype in the database is date/time and the value in the web page is entered as just date. i.e. 2/20/2003 I thought by using the FormatDateTime command on the text box value it would work. <BR><BR>Below is part of my SQL statement with BETWEEN.<BR><BR>dtFirstDate = Request("date1")<BR>dtSecondDate = Request("date2")<BR><BR>SQLstmt = SQLstmt & " AND sc.DateTimeInitiated BETWEEN &#039;" & FormatDateTime(dtFirstDate,0) & "&#039; AND &#039;" & FormatDateTime(dtSecondDate,0) & "&#039;" <BR><BR>Doing a Response.Write of SQLstmt on the page after running it the date still shows up without the time. <BR><BR>LIKE &#039;%[Cc]andace%&#039;OR tech.LastName LIKE &#039;%[Cc]andace%&#039;OR tech.NickName LIKE &#039;%0%&#039;OR emp.NickName LIKE &#039;%0%&#039;OR lo.LocationName LIKE &#039;%0%&#039; AND sc.DateTimeInitiated BETWEEN &#039;2/20/03&#039; AND &#039;2/20/03&#039; ORDER BY tech.NickName;<BR><BR><BR>This is what the DateTimeInitiated looks like in the table.<BR><BR>2/14/03 11:58:14 AM<BR> <BR>Any ideas how to make the Select statement work with an input value from a text box of just date and comparing it with a field in the database that is of date/time?<BR><BR><BR>

  2. #2
    Join Date
    Dec 1969
    Posts
    1,843

    Default RE: SQL Select ......... BETWEEN two Dates

    What DB are you using?<BR><BR>In access/SQL Server this should work:<BR><BR>SQLstmt = SQLstmt & " AND FormatDateTime(sc.DateTimeInitiated,0) BETWEEN &#039;" & FormatDateTime(dtFirstDate,0) & "&#039; AND &#039;" & FormatDateTime(dtSecondDate,0) & "&#039;"

  3. #3
    Join Date
    Dec 1969
    Posts
    62

    Default RE: SQL Select ......... BETWEEN two Dates

    When I add this FormatDateTime(sc.DateTimeInitiated,0) I get this Error message.<BR><BR>Microsoft OLE DB Provider for ODBC Drivers error &#039;80040e14&#039; <BR><BR>[Microsoft][ODBC SQL Server Driver][SQL Server]&#039;FormatDateTime&#039; is not a recognized built-in function name. <BR><BR>/scat/SCATsearch.asp, line 554 <BR>

  4. #4
    Join Date
    Dec 1969
    Posts
    1,843

    Default Sorry....

    I don&#039;t know why I thought FormatDateTime worked in SQL Server. <BR><BR>Here&#039;s a User Defined Function to do what you&#039;re asking for. Add this in SQL Server<BR><BR>CREATE FUNCTION dbo.DatePart<BR> ( @fDate datetime )<BR>RETURNS varchar(10)<BR>AS<BR>BEGIN<BR> RETURN ( CONVERT(varchar(10),@fDate,101) )<BR>END<BR>GO<BR><BR>Then use it like this:<BR><BR>SQLstmt = SQLstmt & " AND dbo.DatePart(sc.DateTimeInitiated) BETWEEN &#039;" & FormatDateTime(dtFirstDate,0) & "&#039; AND &#039;" & FormatDateTime(dtSecondDate,0) & "&#039;"


  5. #5
    Join Date
    Dec 1969
    Posts
    62

    Default RE: Sorry....

    I am still on SQL Server 6.5 which doesn&#039;t support User-Defined Functions. Any other ideas??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •