How to format a date? (1 Viewer)

deletedT

Guest
Local time
Today, 14:40
Joined
Feb 2, 2019
Messages
1,218
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:

2020-07-04_7-59-37.png

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

Thanks.
 

June7

AWF VIP
Local time
Today, 06:40
Joined
Mar 9, 2014
Messages
5,425
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

Guest
Local time
Today, 14:40
Joined
Feb 2, 2019
Messages
1,218
You mean I can not show something like this?
2020/07/03 08:51:33 PM
 

deletedT

Guest
Local time
Today, 14:40
Joined
Feb 2, 2019
Messages
1,218
Dropping AM/PM doesn't give me the result I need either.

2020-07-04_8-36-17.png
 
Last edited:

June7

AWF VIP
Local time
Today, 06:40
Joined
Mar 9, 2014
Messages
5,425
It works for me. If you want to provide db for analysis, follow instructions at bottom of my post.
 

deletedT

Guest
Local time
Today, 14:40
Joined
Feb 2, 2019
Messages
1,218
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

Lifelong Learner
Local time
Today, 07:40
Joined
Mar 14, 2017
Messages
8,738
@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.
time and date.jpg
 

June7

AWF VIP
Local time
Today, 06:40
Joined
Mar 9, 2014
Messages
5,425
My regional setting has single h.
 

deletedT

Guest
Local time
Today, 14:40
Joined
Feb 2, 2019
Messages
1,218
@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

Lifelong Learner
Local time
Today, 07:40
Joined
Mar 14, 2017
Messages
8,738
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

Guest
Local time
Today, 14:40
Joined
Feb 2, 2019
Messages
1,218
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?
 

Attachments

  • Database7.zip
    4 MB · Views: 91

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:40
Joined
Feb 19, 2002
Messages
42,984
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

Lifelong Learner
Local time
Today, 07:40
Joined
Mar 14, 2017
Messages
8,738
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

Guest
Local time
Today, 14:40
Joined
Feb 2, 2019
Messages
1,218
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)

2020-07-04_9-44-12.png

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.
 
Last edited:

June7

AWF VIP
Local time
Today, 06:40
Joined
Mar 9, 2014
Messages
5,425
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

AWF VIP
Local time
Today, 06:40
Joined
Mar 9, 2014
Messages
5,425
However, Format function can force placeholder 0. Unfortunately, the result is a string not a true date value.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:40
Joined
Feb 28, 2001
Messages
27,003
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.
 

deletedT

Guest
Local time
Today, 14:40
Joined
Feb 2, 2019
Messages
1,218
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

Lifelong Learner
Local time
Today, 07:40
Joined
Mar 14, 2017
Messages
8,738
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)
Code:
year(n) & "/" & right("0" & month(n),2) & "/" & right("0" & day(n),2) & " " & right("0" & hour(n),2) & ":" & right("0" & minute(n),2)
 

Users who are viewing this thread

Top Bottom