How to format a date?

[deletedT]

How can I format a date to show the hours as two digits?

This is the format I use, but still the hours shows in one digits:

The form is bound to a sql server database with a timestamp data type.

Thanks.

 

[June7]

Hours with 2 digits would be 24-hr clock and would not show AM/PM. Just drop the AM/PM from the format string.

 

[deletedT]

You mean I can not show something like this?
2020/07/03 08:51:33 PM

 

[deletedT]

Dropping AM/PM doesn't give me the result I need either.

 

[June7]

It works for me. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[deletedT]

I don't have any problem with uploading a simplified version of my db but I don't understand how anything else may interfere formatting a date.

How is your short time and long time format in control panel?
You may have it as hh: Mine is h:

 

[Isaac]

@Tera
I believe that this can be solved by changing windows regional settings. Notice in my screenshot (and this was my computer's default - it might be your computer's default, too--even if your region shows things a little differently) - notice that even in the "long date", mine was set with only one h.

Change it to two h's and I think then your Access setting will work. A bit annoying to have to do this, but should work.

 

[June7]

My regional setting has single h.

 

[deletedT]

@Tera
I believe that this can be solved by changing windows regional settings.

I can not go changing personal settings of all PCs in a company with tens or hundreds of PCs.
I think format is sitting there to prevent the need of changing any settings outside of Access.

 

[Isaac]

I can not go changing personal settings of all PCs in a company with tens or hundreds of PCs.
I think format is sitting there to prevent the need of changing any settings outside of Access.

I completely understand, and don't blame you at all. I am just pointing out that is actually one solution. I was able to reproduce your problem (format set to yyyy/mm/dd hh:mm:ss, but hours still showed 1 digit), and then changing that setting, suddenly the Access setting worked. Maybe you could use automation to do a one time fix on your users' computer, maybe not.

That's the only solution I know, sorry. Apparently that windows setting "over-rides" the Access hh portion.

 

[deletedT]

It works for me. If you want to provide db for analysis, follow instructions at bottom of my post.

Here it is.
Thanks.

Edit: I had to cut off several hundred thousands of records to be able to upload the file.
How much is the max allowed size for uploads here?

 

[Pat Hartman]

I think that the problem is that Access is not seeing the value as a date. Access is very limited in the SQL Server date formats that it recognizes. Make sure your date is defined as datetime in SQL Server. If it is anything else, that is your problem. You will get illogical sorts and compares if you are using strings as dates when the windows settings are AM/PM rather than a 24 hour clock.

The easiest solution is to fix the data type in SQL Server if you are allowed to do that. The second solution, is to update your SQL Server driver but this would have to be done on ALL computers. Your desktop support people should be able to help you with this "push".

The default driver "SQL Server" probably hasn't been updated in 15 years and so it doesn't support any date data type that I am aware of except datetime.

 

[Isaac]

I was able to actually reproduce the original problem by simply having a date time field in Access with the formats at exactly as Tera said and I had the same problem until I changed my regional settings.

 

[deletedT]

I think that the problem is that Access is not seeing the value as a date. Access is very limited in the SQL Server date formats that it recognizes. Make sure your date is defined as datetime in SQL Server. If it is anything else, that is your problem. You will get illogical sorts and compares if you are using strings as dates when the windows settings are AM/PM rather than a 24 hour clock.

The easiest solution is to fix the data type in SQL Server if you are allowed to do that. The second solution, is to update your SQL Server driver but this would have to be done on ALL computers. Your desktop support people should be able to help you with this "push".

The default driver "SQL Server" probably hasn't been updated in 15 years and so it doesn't support any date data type that I am aware of except datetime.

Thanks for your advice. But too many to follow up. Let's walk one step at a time.

1- Sql server shows the data type as datetime. (as I explained in my original post)

2- The database I attached has a local table. The datatype is data/time. Still it shows the same problem.
It proves no sql side problem

3- I have read several on-line articles that shows it's an Access bug(problem) and as @pisorsisaac@gmail.co explains above there's no way except changing control panel's settings. Or adding a query and reading the field from a query not the table.

4- I use DSNLess liked tables in my database.
this is the connection string:
ODBC;DRIVER=SQL Server;SERVER=Server;DATABASE=Receiption;Trusted_Connection=Yes
Is there any tutorial on how to update the driver? or should I use another driver in above connection string?

Thanks again.

 

[June7]

Ok, did more testing. I was wrong. It shows two digits 24-hr clock for pm time but did not use placeholder 0 for am time. It is dependent on computer regional setting. Unfortunate. Has nothing to do with SQLServer.

I think size limit for attaching file is 4mb zip.

 

[June7]

However, Format function can force placeholder 0. Unfortunately, the result is a string not a true date value.

 

[The_Doc_Man]

Well, this is a crazy shot in the dark, but since that is a form, try making the .ControlSource something like Format( CDate(fieldname), "yyyy/mm/dd hh:nn") - even though the fieldname in question is already a date. My thought is to make Access ignore whatever pass-thru information it is getting for that field by overriding the data type locally. If it works, it is a type of "insulation" from the external datatype. If it doesn't work, at least it is a cheap test for a fix.

If this is something that has to be updated, this won't be as helpful as you like either, since that function might stop it from being updateable.

 

[Galaxiom]

The form is bound to a sql server database

BTW Watch out for this bug if you are comparing times.

 

[deletedT]

Well, this is a crazy shot in the dark, but since that is a form, try making the .ControlSource something like Format( CDate(fieldname), "yyyy/mm/dd hh:nn") -

Still the same. Single digit hour.
Thanks for the advice though.

 

[Isaac]

Well I didn't think that a non-updateable expression would be an acceptable solution for you, but if it is, you could just force the issue (I'm sure you already know this but just throwing it out there)

year(n) & "/" & right("0" & month(n),2) & "/" & right("0" & day(n),2) & " " & right("0" & hour(n),2) & ":" & right("0" & minute(n),2)