CODEWITHSUNDEEP
datetimetextms-access-2007
DocDuncan
DocDuncan

Reputation: 19

Convert Access 2007 date/time to text, mm/dd/yyyy to yyyy-mm-dd

I need to convert a Date/Time in Access (it comes from an Oracle database but Access is the only tool I'm allowed to use to access it) to plain text.

I have found lots of web pages that talk about how to convert from text to date, but I have not found any that can convert date to text without having to code a VBA function. I would like to find an Access 2007 way to do this:

SELECT TO_CHAR(TO_DATE(PURGE_DATE ,'mm/dd/yyyy'),'YYYY-MM-DD')
FROM PATRON;

How can I do this? I've been beating my head against this for three hours now.

I did some more searching and found that this works for part of my need:

SELECT format(PURGE_DATE ,'YYYY-MM-DD')
FROM PATRON;

That flips the fields around, but it does not convert it to text.

Upvotes: 0

Views: 12267

Answers (1)

Gustav
Gustav

Reputation: 55856

First:

SELECT Format([PURGE_DATE],"yyyy-mm-dd") FROM PATRON;

will convert PURGE_DATE to text as Format always returns text.

But - referring to your Oracle query - perhaps PURGE_DATE is text and not date.

If so:

SELECT Format(CDate([PURGE_DATE]), "yyyy-mm-dd") FROM PATRON;

Upvotes: 1

Related Questions