Solved Converting a longer date string into short date

[nector]

Is possible to convert a longer date received from a website into a short date in MS Access

Example longer date : Tue, 24 Dec 2024 12:55:03 GMT)

Convert into : 24/12/2024


I have tried this it failed:

Format((Tue, 24 Dec 2024 12:55:03 GMT),"DD\/MM\/YYYY")

Any idea here

We are interested with the actual date only

 

[MajP]

I am assuming that is coming in as a Text value and not a real Date/Time value. The format functions works great on real Date/time fields or things it can convert to a real date time field. You are probably going to have to do some text string manipulation. I will send a function that should work.
However will you need conversion from GMT to another time?

 

[MajP]

This should return a real Datetime value that you can format any way you want, or return only the date portion.

Public Function GetDateFromString(ByVal strDate As String, Optional DateOnly As Boolean = True) As Date
  strDate = Mid(strDate, InStr(strDate, ",") + 2)
  strDate = Left(strDate, Len(strDate) - 4)
  GetDateFromString = CDate(strDate)
  If DateOnly Then GetDateFromString = Int(GetDateFromString)
End Function

to use in a query. Select GetDateFromString([your field name here]), field, field2, from Sometable

This assumes it is always in that format with a "day," and three letter time zone at the end. If you need to convert from other time zones then you need to make a time zone table.

FYI the convert to date, cdate, function can convert lots of strings into a date data type and then you can manipulate as you like.
It cannot figure out
Tue, 24 Dec 2024 12:55:03 GMT
because of the Tue and GMT, but if you strip them off it can
?Cdate("24 Dec 2024 12:55:03") will properly convert.
Once you convert to a real date value you can format it in almost unlimited ways.

 

[The_Doc_Man]

Tue, 24 Dec 2024 12:55:03 GMT

You can convert this in stages.

The GMT doesn't matter initially, though if you have specifics about multiple time zones, you can do something to convert. The "Tue" can also be dropped. If you know the date, you can ask Access what day of the week it would be later.

To drop it, and assuming what you have is a string, you can find that comma and remove it with the MID( string, start, length) function. Remove the GMT while you are at it. The day-of-week prefix and its comma and following space are essentially constant in length, 5 characters, right? And you want to remove them, so the first thing you want to keep is character 6. The GMT is three spaces and the prefix you are dropping is five spaces, so you want to trim eight spaces.

strlength = LEN (date string)
extract = MID( date string, 6, strlength - 8 )
extractdate = CDATE( extract )

Did you get that date/time info from Access? And in what format did you get it? Because if it was available as a date/time variable, use that instead of the long-date string. BUT above I showed you how to get the time into date/time internal format. If you need to modify the date/time to another timezone, you can add or subtract the fraction 1/24 for each hour of adjustment. For New Orleans, which is 90 degrees East longitude, you would convert that via

extractdateNOLA = extractdate - (6/24)

When you are ready, then you can tell Access to use the FORMAT function with either your desired format string or you could also just tell it to use "ShortDate" - which it recognizes.

 

[MajP]

If you want to do this directly in the query without a User Defined Function, you have to chain all of that together.
It is doable but the UDF is much easier to work with and debug. The inline function is probably faster and more portable. Demo shows both.

Format(CDate(Left(Mid([Your Field Name], InStr([Your Field Name], ",") + 2), Len(Mid([Your Field Name], InStr([Your Field Name], ",") + 2)) - 4)), "MM/dd/yyyy")

 

[Pat Hartman]

Rather that use a function that manipulates a string and relies on formatting, why not take advantage of how the date is stored internally as a double precision number? Just convert the double precision number to an integer without rounding and that strips off the time component.

 

[MajP]

Rather that use a function that manipulates a string and relies on formatting, why not take advantage of how the date is stored internally as a double precision number?

Isn't that the crux of the problem? They are somehow scraping this off a website based on what they show that is likely coming in as string.

date received from a website

They need to convert the string to a date or another formatted string. If this was a date to start then their format expression would have worked, but it is imported as a string.

 

[Pat Hartman]

Sorry, I didn't examine the actual string carefully. You do need to use string functions to trim. Also, unless all the dates are GMT, you need to normalize them either to local time or GMT.

 

[nector]

Many thanks to all you have helped to solve this issues I real appreciate you all.