Query - format MS SQL date/time extened that is linked to Access as yyyy-mm-dd

[thefirstbigk]

Greetings -


I'm trying to create a select query in Access that will report a MS SQL Date/Time Extended field formatted as yyyy-mm-dd.


For example:


from a linked MS SQL table - one example record is: 10/31/2022 8:00:15 AM


I would like my query result to be : 2022/10/31


I've tried several Format methods in Access and the result is always: #error


I'm using a local desktop MS SQL Server 2019 and Microsoft® Access® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit if that makes any difference.


Any help or advice would be much appreciated.


BigK

 

[CarlettoFed]

Show the ways you've tried.

 

[Minty]

Which ODBC Driver are you using - the older ones don't handle the DateTime2 format very well, it is often seen as text.
In all honesty, even the newer driver and latest access versions don't play nicely with it.

If you have the option change it back to a basic DateTime data type.

 

[arnelgp]

on MSSMS, create a View from your table:

SELECT ID, CONVERT(varchar, DateTimeFieldHere, 101) AS Expr1, Name
FROM dbo.test_table

save your view.

now, on MSA, link the view.

use the Linked view in your query:

SELECT dbo_View_1.ID, Format([Expr1],"yyyy/mm/dd") AS Dte
FROM dbo_View_1;

you can even create a function to convert it first to real view:

Public Function fnToDate(ByVal var As Variant) As Date
fnToDate = var
End Function

your SQL query:

SELECT dbo_View_1.ID, fnToDate([Expr1]) AS Dte, dbo_View_1.[name]
FROM dbo_View_1;