-
March 25th, 2007, 08:46 AM
#1
Resolved - 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
Last edited by George1111; March 25th, 2007 at 08:59 AM.
-
March 25th, 2007, 09:02 AM
#2
Re: SQL Statement for Selecting Date Range
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
If the post was helpful...Rate it! Remember to use [code] or [php] tags.
-
March 25th, 2007, 10:08 AM
#3
Re: SQL Statement for Selecting Date Range
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'
-
March 26th, 2007, 12:49 AM
#4
Re: SQL Statement for Selecting Date Range
Use the 'GetDateFormat' API to convert the date to System's date format
and then do the comparision
-
April 6th, 2007, 05:11 AM
#5
Re: SQL Statement for Selecting Date Range
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
TheCPUWizard is a registered trademark, all rights reserved. (If this post was helpful, please RATE it!)
2008, 2009,2010
In theory, there is no difference between theory and practice; in practice there is.
* Join the fight, refuse to respond to posts that contain code outside of [code] ... [/code] tags. See here for instructions
* How NOT to post a question here
* Of course you read this carefully before you posted
* Need homework help? Read this first
-
April 6th, 2007, 06:16 AM
#6
Re: SQL Statement for Selecting Date Range
Originally Posted by 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
you mean sql injection
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.
-
April 6th, 2007, 06:29 AM
#7
Re: SQL Statement for Selecting Date Range
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
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.
TheCPUWizard is a registered trademark, all rights reserved. (If this post was helpful, please RATE it!)
2008, 2009,2010
In theory, there is no difference between theory and practice; in practice there is.
* Join the fight, refuse to respond to posts that contain code outside of [code] ... [/code] tags. See here for instructions
* How NOT to post a question here
* Of course you read this carefully before you posted
* Need homework help? Read this first
-
April 10th, 2007, 09:07 AM
#8
Re: SQL Statement for Selecting Date Range
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
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.
-
January 4th, 2013, 06:25 PM
#9
Re: Resolved - SQL Statement for Selecting Date Range
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 .
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|