Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Parcing Year and Month from a string

Status
Not open for further replies.

brhouser

Programmer
Jan 3, 2003
5
US
I have a field, PERIOD_ID, that is in a text format, I am trying to convert this into an actual date format:

an example of what the data looks like in this field is 0208 this correspondes to August 2002.

I have tried the format function by doing this:

format((mid([period_id],3,2) & 20 & left([period_id],2), "mm/yyyy")

I would expect this to return 08/2002, however the dates in the return are way off from the original data in the PERIOD_ID field.

For example, the 0208 returns 07/2124 as the date.

I have tried doing this in 2 separate steps:

format(mid([period_id],3,2),"mm")
format(20&left([period_id],2),"yy")

And get the same results - 07 for MM and 2124 for YYYY (for the 0208 period_id).

I have also tried to convert these into regular integers, then convert into dates, then concatinate them together.

Nothing is working so far, any help would be grateful!

 
You first have to convert the data to a valid date to use format. Cdate works nicely...

Format(Cdate(Right([period_id],2) & "/1/" & left([period_id],2)),"mm/yyyy")

I also used right instead of mid because it should be faster but probably not noticible.
 
Try this to start.
Mid([period_id],3,2) & "/20" & left([period_id],2)

If that gives you what you want and you want the datatype to actually be Date then try this.

CDate(Mid([period_id],3,2) & "/20" & left([period_id],2))

This will return 8/1/2002 and that may not be what you want.

Format(Mid("0208",3,2) & "/20" & left("0208",2), "mm/yyyy")

returns 08/2002 but it will not be a date format, it will be text.


Paul

 
Thanks lameid and Paul - both worked, and I have what I want now.

Thanks again!

Brian
 
Now, any suggestions to how to make the day in the date always be the last day of the month?

 
Add a month to the first day of the month and then subtract a day...

DateAdd("d",-1,Dateadd("m",1,Cdate(Right([period_id],2) & "/1/" & left([period_id],2))))
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top