convert sql string to date in ms access

Question

HI there, I have a Create_Date in sql that results: 2023-03-08 09:58:05.4470000.

I connect the datatabse to SQL backend but would I would like to convert theCcreate_date to the date format mm/dd/yyyy or however 10 character date format that possible.

Any help is appreciated. thank you in advance.

Answer 1

Yes.

Try this more verbose approach:

DateSerial(Year([Create_Date]),Month([Create_Date]),Day([Create_Date]))

Answer 2

Where will this be needed? How will you use it?

Answer 3

In a query, I want to produce a query for the files created yesterday. so the criteria would be Data()-1 in the query design. Unless there is a straight forward way to do this without having to convert the timestamp into date first.

Answer 4

Okay, so I have to make a couple of additional assumptions.

1. This is actual a DateTime2() datatype in SQL Server, correct?
2. You've enabled support for Date Time Extended data in order to accommodate these values as dates in Access, correct?

If so, the DateValue() Expression should accomplish what you need. It expects a string, but it ought to return the date you need from the Extended Date.

But there may be other methods to accomplish what you need, as well.

Also, it's a good idea to be wary of the term "timestamp" when discussing SQL Server tables. There is, in fact, a datatype called TimeStamp, or RowVersion, which not a date value at all. It might be misleading to use the term "timestamp" for something else.

Answer 5

Yes I have enabled both checkmarks.

I have tried the DateValue function and got the error: Data Type Mismatch in criteria expression.