Text to date

[eti3nne]

I have dates in the format of 200804; 200712 etc meaning year 2008 month04; year 2007 month 12 etc.

How can i get these to read as Apr 2008; Dec 2007?

I need to do the conversion in sql, but if you can help me in access I might be able to tranlate the solution?

 

[SeeThru]

how about...

cdate("01/" & mid(mystring,5,2) & "/" & left(mystring,4)

note - this is for UK style dd/mm/yyyy date - just swap the order and the split characters for other formats.

Not tested BTW.

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[SkipVought]

Hi,

This is an unambiguous conversion (not UK nor US based)

YourDat: DateSerial(Mid([YourDateString],1,4),Mid([YourDateString],5,2),Mid([YourDateString],7,2))

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Format(DateSerial(Left([YourDateField],4),Mid([YourDateField],5,2),1), "mmm yyyy")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[eti3nne]

Thanks all for those thoughts. Unfortunately format and mid functions do not work in sql.

I have had a further thought on this... what I actually need is to convert eg '200804' into 'Apr 2008' etc, (with no '1st of the month' attached).

My post is titled 'text to date', perhaps i am approaching/describing this the wrong way; I probably need 'text to text'? - the resulting text being a date of sorts.

Any further thoughts greatfully received.

Thanks for looking.

 

[PHV]

And this ?
MonthName(Right(yourDatefield,2),True) & " " & Left(yourDateField,4)

BTW, which flavor of SQL ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

"Unfortunately format and mid functions do not work in sql."

What database are you coding in?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[eti3nne]

MonthName function isn't available either.

Database is in sql ?2000.

I may have to use a lookup table or hard code a line of code for each of the months, in the DTS.

Cheers,

Steve.

 

[SkipVought]

Maybe post in Forum183 rather than an MS Access forum?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}