Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Posts
    3

    Unanswered: How do I select records that are x days old?

    Being somewhat a newbie, am having trouble developing SQL statement to select records written to tables two days previous to the current date.

    Need to remove data from fields after it's two days old . . .

    Any help or direction to such is appreciated!

    JB

  2. #2
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    You can use the dateadd function for that:

    ... WHERE MyTimestamp <= DateAdd("d",-2, Now())

    limits to records with a timestamp at least 2 days ago.

  3. #3
    Join Date
    May 2002
    Posts
    3
    Thanks for the reply!

    I tried the code you provided but didn't have success.

    My record name is "DateEntered" and is saving not only dates, but times as well. Sample data = 4/26/02 12:01:16 PM

    I am using ColdFusion queries to retrieve the data.

    Here's what I have for query so far, that isn't working:

    <cfoutput>
    <cfquery name="GetCCs"
    datasource="dsarms.com"
    dbtype="ODBC">
    SELECT *
    FROM customers
    WHERE DateEntered <= DateAdd("d",-2, #DateFormat(Now())#)
    </cfquery>
    </cfoutput>

    CFoutput and pound signs (#) were necessary to grab current date from system.

    Thanks again for your help!

  4. #4
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    What exactly is the problem you have?

    You want to select all rows not older than 2 days? I thought from your first post you want to delete the older records.

    It's just the other way around in this case:
    <cfoutput>
    <cfquery name="GetCCs"
    datasource="dsarms.com"
    dbtype="ODBC">
    SELECT *
    FROM customers
    WHERE DateEntered >= DateAdd("d",-2, #DateFormat(Now())#)
    </cfquery>
    </cfoutput>

    I don't think ColdFusion handles the jet-sql though as you seem to connect to access via odbc. You probably need to find an equivalent in coldfusion. I don't have any experience with it so I might be totally wrong as well

    Another problem you might have is:

    do you want to select all rows which were created in the last 48h (that's what the code above actaully does)
    or do you want all rows created within today and the day before?

  5. #5
    Join Date
    May 2002
    Posts
    3
    Thanks (again) for the help . . . I believe I've figured out my problem.

    Following code is doing the trick for me:

    <cfquery name="GetCCs"
    datasource="dsn_name"
    dbtype="ODBC">
    SELECT *
    FROM orders
    WHERE DateEntered <= #DateAdd("d", -2, ParseDateTime(Now()))#
    ORDER BY DateEntered DESC
    </cfquery>

    Think my issue was the fact that there was time in the field added to date. Used the "ParseDateTime" function, and my query's giving me what I need.

    I was originally planning on deleting information from these fields, but now have been asked to replace the first 12 digits with x's, and leave the last four (they're credit card numbers).

    Have any suggestions for that function? If so, your help is appreciated, if not, I'm sure I can flounder my way through.

    Thanks again!

    JB

  6. #6
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    A function to "X-out" a string keeping the last n-digits would look like:

    Code:
    Function fBlankOut( _
        ByVal pvstrIn As String, _
        ByVal pvintRemainingChars As Integer, _
        ByVal pvstrFillChar As String) _
        As String
        
        Dim strPad As String
        
        If Len(pvstrIn) - pvintRemainingChars < 0 Then
            pvintRemainingChars = 0
        End If
        
        strPad = String(Len(pvstrIn) - pvintRemainingChars, pvstrFillChar)
        fBlankOut = strPad & Right(pvstrIn, pvintRemainingChars)
    End Function
    in your case you would write an update query like this:

    UPDATE customers
    SET ccNr = fBlankOut(ccNr,4,'X')
    WHERE DateEntered <= DateAdd("d",-2, #DateFormat(Now())#)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •