SQL Statement for Selecting Date Range

I have a TEXT field which contains a Date in a Table in an ACCESS Database

I want to select records from the table using an SQL Statement

Code:

---

eg, Select * from Table where DateField between #01/01/2004# and #31/07/2004#

---

The problem is that the DateField is treated as a text literal rather than a date, so in reality, every date on file is selected

Is there a trick to make the SQL Statement understand that the text field is in fact a date field and therefore do a date comparison rather than a text comparison ?

eg, Is there a way I can invert the text field (withing the SQL Statement) so I am comparing with a value like 20040525

RESOLUTION

You need to add the cdate function to the string

Code:

---

eg, Select * from Table where cdate(DateField) between #01/01/2004# and #31/07/2004#

---

Thanks

Well, if you have your date format in YYYYMMDD then you are set to go! That is the perfect chronological comparison format.

Code:

---

SELECT * FROM table WHERE DateField >= 20040101 AND DateField <=20040731

---

Or, something like this: It accepts a date field, and formats it. You could write a function that accepts a text field, and you could format it to a date.

Code:

---

Private Function GetRecordset(ByVal pdteSearchDate As Date) As ADODB.Recordset
Dim strSQL          As String
Dim adoRec          As ADODB.Recordset
        strSQL = "SELECT * "
        strSQL = strSQL & "FROM AS_Createsched "
        strSQL = strSQL & "WHERE AS_Createsched.CSIN_dates = #" & Format$(pdteSearchDate, "d mmm yyyy") & "# "
        strSQL = strSQL & "AND HOUR(AS_Createsched.CSIN_times) = " & Format$(pdteSearchDate, "hh") & " "
        strSQL = strSQL & "AND MINUTE(AS_Createsched.CSIN_times) = " & Format$(pdteSearchDate, "nn") & " "
        Set adoRec = New ADODB.Recordset
        adoRec.Open strSQL, GetConnString, adOpenForwardOnly, adLockReadOnly
        Set GetRecordset = adoRec
        Set adoRec = Nothing
End Function

---

I store dates as yymmdd in text format, but use a function to display them correctly on the screen, so clients don't get 'confused'

Use the 'GetDateFormat' API to convert the date to System's date format
and then do the comparision

dglienna, :cry: :cry:

I am suprised at you, that code is so vunerable to attack (even by accident).. SQL command sreing should almost never be done. Always use the appropriate parameter objects.

David

Quote:

---

Originally Posted by TheCPUWizard

dglienna, :cry: :cry:

I am suprised at you, that code is so vunerable to attack (even by accident).. SQL command sreing should almost never be done. Always use the appropriate parameter objects.

David

---

you mean sql injection :rolleyes:

Code:

---

Private Function GetRecordset(ByVal pdteSearchDate As Date) As ADODB.Recordset
Dim strSQL          As String
Dim adoRec          As ADODB.Recordset
 strSQL = "SELECT * "
 strSQL = strSQL & "FROM AS_Createsched "
 strSQL = strSQL & "WHERE AS_Createsched.CSIN_dates = #" & Format$(
pdteSearchDate
, "d mmm yyyy") & "# "
strSQL = strSQL & "AND HOUR(AS_Createsched.CSIN_times) = " & Format$(pdteSearchDate, "hh") & " "
strSQL = strSQL & "AND MINUTE(AS_Createsched.CSIN_times) = " & Format$(pdteSearchDate, "nn") & " "
Set adoRec = New ADODB.Recordset
adoRec.Open strSQL, GetConnString, adOpenForwardOnly, adLockReadOnly
 Set GetRecordset = adoRec
Set adoRec = Nothing
End Function

---

this piece of code no problem,GetRecordset function parameter is data type Date.
Although Obviously is best make it using placeholder, parameter of Microsft Access, store procedure or Validating input before we build SQL statement.

That is the form of attack I was referring to. And that code is NOT 100% safe. There known buffer overflows (in VB6 fix for VB7) that can corrupt the format routines in use :cry:

Also allows using a stored proc, allows the DMBS to analyze its execution plan ONCE, and may be significantly faster.

And the new FREE builds of SQLServer are significantly faster than Access/Jet in almost all cases, so if you are coding your entire GUI in VB6 and noy using any access features other than the MDB to hold DATA, I recommend checking it out.

Quote:

---

Originally Posted by TheCPUWizard

That is the form of attack I was referring to. And that code is NOT 100% safe. There known buffer overflows (in VB6 fix for VB7) that can corrupt the format routines in use :cry:

Also allows using a stored proc, allows the DMBS to analyze its execution plan ONCE, and may be significantly faster.

And the new FREE builds of SQLServer are significantly faster than Access/Jet in almost all cases, so if you are coding your entire GUI in VB6 and noy using any access features other than the MDB to hold DATA, I recommend checking it out.

---

Ok,TheCPUWizard

thanks for infomation about it.

Quote:

---

Originally Posted by George1111

I have a TEXT field which contains a Date in a Table in an ACCESS Database

I want to select records from the table using an SQL Statement

Code:

---

eg, Select * from Table where DateField between #01/01/2004# and #31/07/2004#

---

The problem is that the DateField is treated as a text literal rather than a date, so in reality, every date on file is selected

Is there a trick to make the SQL Statement understand that the text field is in fact a date field and therefore do a date comparison rather than a text comparison ?

eg, Is there a way I can invert the text field (withing the SQL Statement) so I am comparing with a value like 20040525

RESOLUTION

You need to add the cdate function to the string

Code:

---

eg, Select * from Table where cdate(DateField) between #01/01/2004# and #31/07/2004#

---

Thanks

---

Thank It has helped me .