CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,900

    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.

  2. #2
    Join Date
    May 2002
    Posts
    10,943

    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.

  3. #3
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    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'
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  4. #4
    Join Date
    Dec 2006
    Location
    Pune, India.
    Posts
    579

    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

  5. #5
    Join Date
    Mar 2002
    Location
    St. Petersburg, Florida, USA
    Posts
    12,125

    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

  6. #6
    Join Date
    Sep 2006
    Posts
    635

    Re: SQL Statement for Selecting Date Range

    Quote 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.

  7. #7
    Join Date
    Mar 2002
    Location
    St. Petersburg, Florida, USA
    Posts
    12,125

    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

  8. #8
    Join Date
    Sep 2006
    Posts
    635

    Re: SQL Statement for Selecting Date Range

    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

    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.

  9. #9
    Join Date
    Jan 2013
    Posts
    1

    Thumbs up Re: Resolved - SQL Statement for Selecting Date Range

    Quote Originally Posted by George1111 View Post
    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
  •  





Click Here to Expand Forum to Full Width

Featured