Wrong Day of Week

[jazzy]

access office 365. linked table to Azure SQL. data type is set to Date/Time Extended. shows date 10/13/2022 (Thursday) but when i put the format as Long Date it shows the wrong day of the week (Friday).



SQL is showing Data Type datetime2(0)

any ideas?

[Welshgasman]

Look at the syntax. As one of the arguments is to specify the first day of the week?

[ranman256]

format("10/13/2022 13:55","ddd")
should be THU


WEEKDAY( "10/13/2022 13:55")
should be 5 (thurs)


sometimes access gets confused on sql date, you could try trim the value to the true date LEFT([field],....) , then get the DoW.

[jazzy]

it was working fine for months, then all of a sudden yesterday it started happening. only the new version is screwed up, the other versions are working fine. no code was changed on this form. totally confused. if i run a simple query on the table and chnage format to Long Date its incorrect. is it possible that the time is missing that is causing the problems? '13:55' ?

it seems that Access has changed the new linked tables date fields from Date/Time to Date/Time EXTENDED. is there a way to stop that?

[jazzy]

problem solved....Set user options for the current database (microsoft.com)

Access File -> Options -> Current Database had 'Data Type Support Options' and Yes was clicked for Support Date Time Extended for Linked/Imported Tables.

[Welshgasman]

So are you saying that a time element makes the function see the day as the next day?

[jazzy]

Thats what happened. confuses the hell out of me. it changed all the date/time fields to date/time extended when i linked the databases. then when i used long format the day of the week was off by one day.

it also wouldn't calculate time. =Format(1+[EndTime]-[StartTime],"Short Time")

NUTS!!!