Limit date to one day

• Dave Bailey

  SSC Veteran

  Points: 216

  More actions

  November 3, 2005 at 11:13 am

  #92007

  Following is a snippet of code:

   

  strSQL = "Insert into " & rst!TableName & _

          " In '' [ODBC;DSN=MetaSys;UID=MetaControl;PWD=Tootsiev.4;Database=MetaSys;] " & _

          "Select DateSerial(([DATE_Y]-100),DATE_M,DATE_D) + " & _

          "TimeSerial(TIME_H,TIME_M,0) as [dStamp]," & _

          "VALREAL as [MValue]" & _

          "FROM 128 IN '" & rst!altdBasePath & "' [dBASE IV;] " & _

          "WHERE VALID = 1 AND RELIABLE = 1 " & _

          "AND DATE_NDX*10000+TIME_NDX > " & ((Format(rst!MaxSQLdate, "yymmddhhnn")) + 10000000000#) & " ;"

   

  I wish to limit the date to 10/28/05 only.  How do I alter the above code to limit date to 10/28/05

  Thanks,

  Dave

• rsharma

  SSCarpal Tunnel

  Points: 4865

  More actions

  November 3, 2005 at 12:16 pm

  #602210

  CREATE TABLE TESTCASE (COL1 DATETIME)

  GO

  INSERT INTO TESTCASE VALUES (GETDATE())

  INSERT INTO TESTCASE VALUES (GETDATE())

  INSERT INTO TESTCASE VALUES (DATEADD(DD, 1, GETDATE()))

  INSERT INTO TESTCASE VALUES (DATEADD(DD, 2, GETDATE()))

  GO

  SELECT * FROM TESTCASE

  --Output

  COL1                                                  

  ------------------------------------------------------

  2005-11-03 13:56:40.120

  2005-11-03 13:56:40.120

  2005-11-04 13:56:40.120

  2005-11-05 13:56:40.120

  (4 row(s) affected)

  --For a specific day:

  SELECT * FROM TESTCASE

  WHERE COL1 >= '2005-11-03'

  AND COL1 < '2005-11-04'

  --Output

  COL1                                                  

  ------------------------------------------------------

  2005-11-03 13:56:40.120

  2005-11-03 13:56:40.120

  (2 row(s) affected)

  And you should index that column so that the search on the range is faster.