Formatting date field using SQL

[Euler]

I want to use SQL or VBA to format a date field in my table.

Right now, the dates look like this: 02/12/18 5:19:50 PM
But I need them to look like this: Mon, 12 Feb 2018 17:19:50

The following works correctly when I manually enter it into the field's format property: ddd", "dd mmm yyyy hh:nn:ss
But I'd like to automate it so I can use SQL or VBA to format the field.

I tried to use the format function: Format("MyTime", "ddd', 'dd mmm yyyy hh:nn:ss") but it doesn't work. Maybe it's something to do with the quotation marks.

I also tried to change the property with this statement: CurrentDb.TableDefs("MyTable").Fields("MyTime").Pr operties("Format") = "ddd', 'dd mmm yyyy hh:nn:ss" but I just got the message that the property wasn't found.



There must be a way to do this. I'd be grateful for any suggestions. Thanks for your time.

[Gicu]

Could you please try this:

Format("MyTime", "ddd, dd mmm yyyy hh:nn:ss")

Cheers,

[Euler]

Sorry, but I get this message:


Can't update due to datatype

[June7]

What message?

Is MyTime the actual name of your field? Don't use quote marks around field name.

Your original attempt with Format() function had unnecessary apostrophes.

I wouldn't bother with setting property in table. Set it in textbox, no outer quote marks: ddd", "dd mmm yyyy hh:nn:ss

[Gicu]

Sorry, I haven't specified that you would use that in a query if you need to display the date field per your example. Be aware that using the format function will change the data type to string. You do not "update" the table with the format, it is used just for displaying, the table stores the dates as a datetime (which is really a double data type).
Here is some reading about it:
https://codekabinett.com/rdumps.php?...ype-vba-access
Cheers,

[Euler]

OK, thanks. I didn't know I couldn't use the format function to update a field.

The problem remains: How then do I change the format for the dates in that field using SQL or VBA?

[June7]

You can calculate format in query with Format() function - as noted the result is a string, not a true date/time value so why do it? Or use Format() function in VBA but why do you need it in VBA? Why bother formatting in table?

Could just simply set Format property in textbox design. This does not affect the value, it remains a date/time.

[Micron]

Isn't the answer in post 4? No sql or code required for such a format when viewing on a form. You set the control format property as suggested. You should not care what it looks like in a table as no one should be poking around in tables when using the db. If you're trying to do this in a query, IMO it's the same thing as a table - you should not. A select query is basically a filtered (or not) view of a table and much of the time they behave exactly the same way.

[CJ_London]

better not to format any fields in tables - they will mask what you actually have.

some properties do not exist until they are created - format being one of them. So if you must, just go into table design and set the property manually.

If you use the format function, that will convert a date numeric datatype to text so a) you cannot update a date field to a text value and b) text values sort and filter in a different way to numeric values

and the code should be

Format([MyTime], "ddd, dd mmm yyyy hh:nn:ss")

otherwise you are trying to format the word 'mytime'

Presume you are aware that a date field is stored as a decimal number, the part before the decimal point is the number of days since 31/12/1899 and the bit after the number of seconds to now in the day divided by 86400 (total number of seconds in a day. So right now

?cdbl(now())
44351.0027893518

[Euler]

Isn't the answer in post 4? No sql or code required for such a format when viewing on a form. You set the control format property as suggested. You should not care what it looks like in a table as no one should be poking around in tables when using the db. If you're trying to do this in a query, IMO it's the same thing as a table - you should not. A select query is basically a filtered (or not) view of a table and much of the time they behave exactly the same way.

Sorry, I need to do it the way I described. After I format it correctly I then will change the datatype to TEXT and add the initials "GMT" to the end of each string.

[CJ_London]

the format function creates a string, so you need to change the datatype before you convert it, better to create a new column. And you don't need to add GMT as a separate action, just do it at the same time as you format

?format(now(), "ddd, dd mmm yyyy hh:nn:ss") & " GMT"
Fri, 04 Jun 2021 08:37:45 GMT

Be aware that this string cannot be converted back to a date - if you want to sort in date/time order for example - unless you strip out the day and GMT

Do not understand why you want to save this value in a table and not just use it as and when - in a query for example

FormattedDate: Format([myDateTime, "ddd, dd mmm yyyy hh:nn:ss") & " GMT"

[Minty]

As everyone else has stated, you shouldn't store a datetime as a string.
You can't search or filter on a date or time range that is stored as a string.

Simply add a the string version as a output to your query or simply format it that way in a form control to view it.

What purpose does converting it and storing it achieve?
Is there something we are all missing here?

[Micron]

Sorry, I need to do it the way I described.

No, you want to do it the way you describe in spite of what everyone is telling you. IMO the best thing to do now is for someone to show you how to do what you want. Then at a later date you can come back and start a new thread about the problems it raises.

[Minty]

I'll do it - I'm waiting for a call that's late so no time like the present, then we can all say "We told you" in a week or two

Create a new field text field in your table called txtDateString

Create a new query :

UPDATE MyTable

SET txtDateString = Format([MyTime], "ddd, dd mmm yyyy hh:nn:ss")

Run the query.

You now have a field in your table with the data you require.

Now Copy and Back up you database.

Open the database and then in the design view of the table


Delete the original column [MyTime]

Save the table

Rename the txtDateString Column to MyTime



You have your desired result.

Keep the back up safe for when you need to do something with the date time data in a few days time.

[Micron]

@Minty, did you forget the GMT?