CODEWITHSUNDEEP
ms-accessms-access-2007
user1955215
user1955215

Reputation: 743

Convert date time string to date

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?

Upvotes: 2

Views: 21297

Answers (2)

HansUp
HansUp

Reputation: 97101

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.

Upvotes: 3

user1955215
user1955215

Reputation: 743

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

Upvotes: 1

Related Questions