Convert text field to date field

[rooley]

After searching through the treads on this topic, I needed help finding a post that addressed this question clearly.

I receive a .cvs file every morning from my HVAC system controller that gives me time-specific readings of all the mechanical equipment in the building. There are generally 6,000 records that look like (/SBMA/ArtStorage105_temp,14-Oct-23 8:45 AM PDT,68.5)---First Field: location of the sensor, Second Field: date and time, Third Field: value.

These readings are taken every 15 minutes for both temperature and humidity. These records are imported to Access as text; the only field that makes sense is the sensor location. The other two fields must be converted to date and number.

I have tried to convert the data on import, but that only deletes the data once imported. I have tried a few update functions but the PDT gets in the way...

SO, I turn to the forum for help.

 

[Gasman]

? cdate("4-Oct-23 8:45 AM")
04/10/2023 08:45:00

? cdate(Replace("4-Oct-23 8:45 AM PDT","PDT",""))
04/10/2023 08:45:00

 

[Mike Krailo]

That almost works Gasman, but this gets rid of the PDT part.

cdate(replace("4-Oct-23 8:45 AM PDT"," PDT","",1))

 

[Gasman]

That almost works Gasman, but this gets rid of the PDT part.

cdate(replace("4-Oct-23 8:45 AM PDT"," PDT","",1))

That was the intention Mike, as the CDate will not work with that in?

I have tried a few update functions but the PDT gets in the way.

 

[Pat Hartman]

You might want to add the time zone to a separate column or possibly even use it to convert the time to GMT for all locations depending on what you need to do with the time values. It won't make much sense to have dates from different time zones all being processed together.

 

[rooley]

cdate(replace("4-Oct-23 8:45 AM PDT"," PDT","",1))

I presume then that I could replace the "date" with the field name?

 

[rooley]

You might want to add the time zone to a separate column or possibly even use it to convert the time to GMT for all locations depending on what you need to do with the time values. It won't make much sense to have dates from different time zones all being processed together.

Time Zone is not important as I am only concerned with one facility.....if I have multiple facilities in multiple countries.....maybe.

 

[Mike Krailo]

I presume then that I could replace the "date" with the field name?

Yes sir, that's exactly what you would do.

 

[rooley]

Yes sir, that's exactly what you would do.

such as...
cdate(replace("[FieldName]"," PDT","",1))

 

[rooley]

Yes sir, that's exactly what you would do.

Source field is text
Destinantion field is date

I get a type mismatch

 

[MajP]

You can get the type mismatch if you get a string that cannot be converted to a date. In this case if you had another time zone PDT, EST, MT, CT etc this will fail. Do all dates have PDT or do some have something else? If they have something else this would fail.
Can you paste a list of dates?

 

[rooley]

You can get the type mismatch if you get a string that cannot be converted to a date. In this case if you had another time zone PDT, EST, MT, CT etc this will fail. Do all dates have PDT or do some have something else? If they have something else this would fail.
Can you paste a list of dates?

All records have the following structure.
(/SBMA/ArtStorage105_temp,14-Oct-23 8:45 AM PDT,68.5)

Sensor Zone, Date of Reading, Value of Reading

Sensor Zone has two entries per reading time....TEMP and RH. Readings for each are taken every 15 minutes.

I don't care about the PDT....as all of my sensors are located in a single building.

 

[Mike Krailo]

What MajP said is true. I assumed you had dates like you showed in your example. You could just create a function that converts every possible time zone or likely time zone into a proper date without the time zone. Use the instr function to test for the presence of a particular timezone.

So you need to either scan through your date data and make sure there is no other timezone string in it, or create a function to account for all the likely timezones that would appear in the data.

 

[plog]

Validate data before converting and updating it:

SELECT [Date of Reading], Replace("[Date of Reading]"," PDT","",1)) AS DateString
FROM YourTableNameHere
WHERE IsDate(replace("[Date of Reading]"," PDT","",1)) = False

If you have any "dates" that aren't dates that query will show them. Fix them then run your UPDATE query. I suggest you validate the field you are converting to a number as well.

 

[MajP]

I make a public function for this.

Public Function GetDateFromString(ByVal varDate As Variant) As Variant
  Dim aDate As String

  If Not IsNull(varDate) Then
    varDate = Trim(varDate)
    varDate = Trim(Split(varDate, ",")(1))
    varDate = Trim(Split(varDate, ",")(0))
    If InStr(varDate, " AM ") > 0 Then
      varDate = Left(varDate, InStr(varDate, " AM "))
    ElseIf InStr(varDate, " PM ") > 0 Then
      varDate = Left(varDate, InStr(varDate, " PM "))
    End If
    If IsDate(varDate) Then GetDateFromString = CDate(varDate)
  End If
End Function

Public Sub testGetDate()
  Dim x As String
  x = "/SBMA/ArtStorage105_temp,14-Oct-23 8:45 AM PDT,68.5"
  Debug.Print GetDateFromString(x)
  x = "/SBMA/ArtStorage105_temp456, 16-Nov-23 12:45 PM PST,78.5"
  Debug.Print GetDateFromString(x)


End Sub

A better function would be regular expressions, but this works on the example data and permutations of it. Assumes commas
sensorzone comma datetime comma reading

 

[arnelgp]

using query to update the [DateField]:

Update yourTable Set [DateField] = CDate(Left(Mid([TextField],Instr(1,[TextField],",")+1), Instr(1,Mid([TextField],Instr(1,[TextField],",")+1), "PDT")-1))