Converting text to date

[dkelley]

I am importing data from a date field formatted as March 9, 2006. I can strip out the month and year using the middle and right function, but cannot extract the day of the month due to the varying length of the month names and lack of leading zeros. Can anyone help?

Thanks

[LaRetta]

Well I'm unsure what you have. It sounds like you are converting each field independently? Because you can have a standard date field set to Auto-enter with:

Let(

[

m = 1 + Div(

Position ( "January__February_March_____April____May______June_____July_____August___SeptemberOctober__November_December_" ; LeftWords(textDate ; 1 ) ; 1 ; 1 ) - 1 ; 9 ) ;

d = MiddleWords(textDate ; 2 ; 1 ) ;

y = RightWords ( textDate ; 1 )

] ;

Date(m;d;y)

)

I would bet there are shorter ways as well ... such as using a Value List of the months, etc. But the date can be converted in one whack! This cute tid-bit of using Position() and set number of characters (filling in with underlines until it equals 9) was picked up from -Queue-.

UPDATE: There are only two plain underlines between October and November but it looks like it's underline-space-underline. Not sure if it's my display; my eyes; my mind; or the forum. :

L

Edited February 12, 2006 by Guest
Added Update

[Raybaudi]

... but cannot extract the day of the month ...

To extract the day of the month use this calc:

MiddleWords ( importedDate ; 2 ; 1 )