Solved

Convert String to Date DDMMMYY Format

Posted on 2011-02-28
6
922 Views
Last Modified: 2012-05-11
I have a field with strings like:
"20 ML @ 26FEB11 1541"
"34 GM @ 26FEB11 1541"
"1 MG @ 26FEB11 1541"

which I need to query on to get the records with the last 36 hours. What's the best way to extract the date/time so I can apply the WHERE clause: Between DateAdd("h",-36,Now()) And Now()
Thanks!

MV
0
Comment
Question by:Michael Vasilevsky
6 Comments
 
LVL 9

Expert Comment

by:sshah254
ID: 35003818
Create a separate field of the type "datetime" (or "date") - make sure that you index it.

It might be easier if you do this parsing in VBA (as opposed to doing it in SQL).

Then run the SQL on that field.

sample code - please debug and test

dim dbs as database
dim rst as recordset
dim str1 as string

set dbs = currentdb
set rst = dbs.openrecordset("select stringdate, datedate from table") 'stringdate is the existing field, datedate is new date type date
if rst.recordcount > 0 then
  rst.movefirst
  with rst
    do while not .eof
      str2 = ''
      str1 = rst!stringdate 'we get 34 GM @ 26FEB11 1541"
      str1 = mid(str1, instr(1, str1, "@ ")+2) 'we get 26FEB11 1541
      select case mid(str1,3,3)
      case "JAN"
        str2 = "01/"
      case "FEB"
        str2 = "02/"
      .......and so on till "DEC"
      end case
      str2 = str2 & mid(str1, 1, 2) & "/20" & mid(str1, 6, 5) & ":" & right(str1, 2) & ":00" 'mm/dd/yyyy hh:mm:00
      .edit
        rst!datedate = str2
      .update
      .movenext
    loop
  end with
end if
set rst = nothing
set dbs = nothing

Ss
0
 
LVL 3

Expert Comment

by:JAMcDo
ID: 35004453
I agree with 'sshah254' that a VBA function is a good thing to consider.  Combining your request with the VBA function, I am suggesting the following function:

Function L36H(FS As String) As Boolean

    Dim P As Date
    Dim intA, intS As Integer
    Dim strM, strT, strYY, strMMM, strDD As String
       
    intA = InStr(1, FS, "@") + 2                    'Locate the @ sign + 2
    intS = InStr(12, FS, " ")                           'Locate the space after the date
    strM = " " & Mid(FS, intA, intS - intA)      'Extract the Date (Pad with 1 space in case of 1-digit day)
    strT = Left(Right(FS, 4), 2) & ":" & Right(FS, 2)   'Extract the Time as hh:mm
    strYY = Right(strM, 2)                              'Extract the 2-digit Year
    strMMM = Left(Right(strM, 5), 3)               'Extract the 3-char Month
    strDD = Left(Right(strM, 7), 2)                  'Extract the 2-digit Day
   
        'Convert to Date & Time
   P = Format(strDD & " " & strMMM & " " & strYY & " " & strT, "dd MMM yy hh:mm")

   If Now() - P <= 1.5 Then                            'Test if in the last 36 hours
        L36H = True
    Else
        L36H = False
    End If
   
End Function

In your query, add a column to test the field and test for TRUE.  i.e.  Test:  L36H([fieldname]) with the Criteria = TRUE

John
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 35006176
Just extract the date/time value from that field using Format and CDate:

DateTime: CDate(Format(Right([YourCode@DateTimeField],12),"!@@ @@@ @@@@@:@@"))

Then apply your filter to this column.

/gustav
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 10

Author Comment

by:Michael Vasilevsky
ID: 35008410
Thanks all I knew I could parse the data with a VBA function and force a date but gustav's is the elegant solution I was looking for.
Thanks!

MV
0
 
LVL 10

Author Closing Comment

by:Michael Vasilevsky
ID: 35008414
perfect
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35008948
You are welcome!

/gustav
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Error message when attempting to run query 14 29
Syntax error with between dates 6 19
Stored Procedure 2 0
Data Conversion 11 0
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now