How to convert a string to date and extract values in Access query

Question

I'm using Access DB 2007 - 2010; I've tried to import many CSV files but the timestamp column keeps failing to import correctly.

So I linked all of the CSV's to an Access DB and I'm trying to query all of the tables.

I'm trying to extract the year and day of the year from the time stamp (which is currently a string)

I'm trying to combine the Format with datepart functions and it keeps failing. (it just says error in the table)

The format function by itself works but I can't combine it with anything.

I'm basically trying to do this:

select datepart("y", Format(gmt, "dd-mmm-yyyy hh:nn:ss")) as DOY from Table1;

but it fails. I've also tried CDate and DateValue in different combinations but it all fails.

Does anyone know how to get this to work?

UPDATE

The format function isn't doing anything. The text remains the same no matter how I try to format it.

Here's a datetime sample: 05-Dec-2008 13:40:01.955

Answer 1

Access can't cope with the milliseconds in your date strings.

Use Left() to exclude them and feed the resulting substring to CDate().

SELECT CDate(Left(gmt, 20)) AS date_from_string
FROM Table1;

Once you have a valid Date/Time value, you can use Year(<Date/Time value>) or DatePart("yyyy", <Date/Time value>) to extract the year. And DatePart("y", <Date/Time value>) will give you the day of the year.