Help with date conversion

[eerich]

Hello,

I've search the threads but could not find a solution to a date conversion issue I'm having. I'm trying to convert a string value to a date. The string is setup as:

fieldnme
2007-09-24 00:00:00.000

My sql is:

Select fieldnme, cdate(format(fieldnme, 'yyyy-mm-dd') as PostedDate
From Table;

I receive an error (#error) in the PostedDate field. I've tried adding double quotes around the format as well but it still produces the same error.

Any help would be appreciated.

 

[CaptainD]

Try

Select fieldnme, format(cdate(fieldnme), 'yyyy-mm-dd') as PostedDate
From Table;

 

[eerich]

Hi Captain D - thanks for the reply.

It still didn't work. Here's my query:

select format(cdate(posteddate), 'yyyy-mm-dd') as EntryDate
from Table1;

I receive a #error.

As noted in my first post, the string stored in each record is in the format:

PostedDate
2007-09-24 00:00:00.000
2008-02-13 00:00:00.000

 

[dhookom]

Try

Select fieldnme, cdate(Left(fieldnme,10)) as PostedDate
From Table;

Duane MS Access MVP

 

[eerich]

Duane,

That worked!

Thanks

 

[CaptainD]

I ran these and no error, do all your fields have dates?

A field with a NULL value would throw an error.

SELECT Format(CDate(tDateTest.sDate),"MM-DD-YYYY") AS MyDate
FROM tDateTest;

AND

SELECT Format(CDate(tDateTest.sDate),"YY-MM-DD") AS MyDate
FROM tDateTest;

 

[PHV]

I ran these and no error
Really ?
CDate("2007-09-24 00:00:00[!].000[/!]") should raise error 13 ...

 

[dhookom]

I got the same error a PH. In addition, the original question asked "I'm trying to convert a string value to a [red]date[/red]".

Using the Format() function on a field converts the value to a variant string rather than a date data type.

Duane MS Access MVP

 

[CaptainD]

I only entered my dates like this "2007-09-24 00:00:00", NO ".000