Convert varchar to Date

system · September 30, 2010, 3:33pm

Hi,
I have a column in DB which is a varchar field and hold the dates like Date: 9/1/2010 2:25:36 PM , it is loaded
into the system as varchar now in my report I need to use the date range

= [date] between (‘9/1/2010’;‘9/30/2010’)

I tried todate ,it dn’t work out some are comming up right but some are showing #error

=ToDate(trim(left([Date];10));“MM/d/yyyy”) -----This dnt work

Please help me out.Thanks in advance.

madhu@P (BOB member since 2008-08-04)

system · September 30, 2010, 3:40pm

Try this:

=SubStr([Date];Pos([Date];1;" ")-1);"M/d/yyyy")

MichaelWelter (BOB member since 2002-08-08)

system · September 30, 2010, 5:16pm

Thankyou so much

madhu@P (BOB member since 2008-08-04)

system · September 30, 2010, 5:25pm

I just realized I didn’t finish the formula. Here is a complete version:

ToDate(SubStr([Date];Pos([Date];1;" ")-1);"M/d/yyyy")

MichaelWelter (BOB member since 2002-08-08)

system · September 30, 2010, 7:46pm

Just in the right time , Thanks again

madhu@P (BOB member since 2008-08-04)