Convert from text to date

[Dewey2424]

I have an ODBC link set up between a DB2 table and MS Access. A date column from the DB2 table is coming through as a text column in Access. I can't change the datatype in design view in Access. Is there a way to convert the text into a date format in a query?

This is an example of the text formatted date. I want to convert to something as simple as "2001-12-14". want to be able to run some easier queries for date ranges but this text format is giving me trouble.

2001-12-14-02.20.07.676990

Thanks,
JG

 

[simongallop]

Type this in a new column in the field box

NewDate: DateValue(Left(DateFieldName,10))

This is presuming that the date aspect will always be 10 characters long ie 1st Jan is 2002-01-01-........

HTH

 

[Pat Hartman]

If you use DB2Connect, you can select the patch that will allow Access to view this column as a proper Date/Time data type. Unfortunately, I cannot be more specific. My current client does not use DB2Connect so I can't look it up for you. If you don't have DB2Connect available to you, the DB2 DBA may be able to resolve the problem at the gateway.

 

[Dewey2424]

Thanks to both of you. I am using the Date Value workaround for now and will see if the DBA can add that patch. I found which one it is. Thanks again.

 

[raskew]

Hi -

You might try playing around with this (from the debug window):

'********************************************
x = "20110315"
y = datevalue(mid(x,5,2)& "/" & mid(x, 7,2) & "/" & left(x,4))
z = format(y, "mmmm yyyy")
? z
March 2011

'********************************************

Best wishes -- Bob

 

[Brianwarnock]

Hi Bob, I'm a bit puzzled as this looks like you are replying to Blackops on this thread rather than this ancient thread.

Brian

 

[raskew]

Thanks Brian -

I apparently suffered a serious brain cramp.

Sorry if I threw unintended confusion into the pot.

Best wishes - Bob