SQL Statement for Selecting Date Range

[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

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

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

Thanks

[PeejAvery]

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

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

[dglienna]

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.

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'

[Shaikh.Riyaz.a]

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

[TheCPUWizard]

dglienna,

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

[hensa22]

dglienna,

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

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.

[id_ruben]

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

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

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

Thanks

Thank It has helped me .