Ms-access – Convert date time string to date

ms-accessms-access-2007

MS Access Table January2015 has a txndate field with the string "2015-01-01 11:48:00"

The field type is text.

The string needs to be converted to date/time i.e. it should appear in the same format but as a time.

Running this query:
SELECT Format(datevalue(txndate), "dd-mm-yyyy hh:mm:ss") FROM January2015;

gives the output:
01-01-2015 00:00:00
(the time part is being ignored).

How can I fix this?

Best Solution

You can get your desired result with one Format() instead of two.

SELECT Format(CDate(txndate),"dd-mm-yyyy hh:nn:ss") AS Expr1
FROM January2015;

Actually Format() will accept your ymd date string without the need to first convert it to Date/Time, so you could eliminate CDate() if you prefer.

SELECT Format(txndate,"dd-mm-yyyy hh:nn:ss") AS Expr1
FROM January2015;

Note however the datatype of that calculated field will be text, not Date/Time because Format() always returns a string.

Related Question