Date Conversion

[Guest]

Hi,

I was wondering if someone could help me. I have a linked table to my
database which has a date field, however this is stored as text (e.g.
060401). I would like to format it to DD/MM/YY (e.g. 06/04/01) I have tried
using the following expression in my query:

CDate(Format([WARRE],"Short Date")) but I end up with the date looking like
this:14/05/65.

Help!!

Thanks

 

[George Nicholson]

NewDate=DateSerial(Right([JulianDate],
Mid([JulianDate],3,2),Left([JulianDate],2))

If JulianDate = "060401" then NewDate value s/b April 6, 2001. How that
value is formated is up to you.

HTH,

 

[John Spencer (MVP)]

That will give you date that is in the year 1 not the year 2001. Easy
assumption all dates are in the year 2000. Add 2000 to the year portion. More
complex add 1900 or 2000 depending on what you decide is the cutover date
1

NewDate=DateSerial(CInt(Right([JulianDate],2)) +
IIF Cint(right([JulianDate],2) < 30,2000,1900),
Mid([JulianDate],3,2),Left([JulianDate],2))

You can try the simpler

DateValue(Format(JulianDate,"@@\/@@\/@@"))

That should work to give you a valid date assuming that your date settings are
mm/dd/yy in your OS.

TEST TEST TEST on a COPY. If it works then use it.

NewDate=DateSerial(Right([JulianDate],
Mid([JulianDate],3,2),Left([JulianDate],2))

If JulianDate = "060401" then NewDate value s/b April 6, 2001. How that
value is formated is up to you.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Hi,

I was wondering if someone could help me. I have a linked table to my
database which has a date field, however this is stored as text (e.g.
060401). I would like to format it to DD/MM/YY (e.g. 06/04/01) I have
tried
using the following expression in my query:

CDate(Format([WARRE],"Short Date")) but I end up with the date looking
like
this:14/05/65.

Help!!

Thanks

 

[Guest]

If you wont get any other better answer then you can use this

CvDate(left([Fieldname],2) & "/" & mid([Fieldname],3,2) & "/" &
right([Fieldname],2))

 

[Van T. Dinh]

Hi John

DateSerial actually observes the "cut-over" year setting. From the A2002,
Debug window:

?DateSerial(0,1,1)
01/01/2000

?DateSerial(1,1,1)
01/01/2001

?DateSerial(29,1,1)
01/01/2029

?DateSerial(30,1,1)
01/01/1930

?DateSerial(99,1,1)
01/01/1999

 

[John Spencer (MVP)]

Well, I'll be.

It is a GOOD day. I've learned something new.

REJOICE! and be glad.

Thanks.

Hi John

DateSerial actually observes the "cut-over" year setting. From the A2002,
Debug window:

?DateSerial(0,1,1)
01/01/2000

?DateSerial(1,1,1)
01/01/2001

?DateSerial(29,1,1)
01/01/2029

?DateSerial(30,1,1)
01/01/1930

?DateSerial(99,1,1)
01/01/1999

--
HTH
Van T. Dinh
MVP (Access)

That will give you date that is in the year 1 not the year 2001. Easy
assumption all dates are in the year 2000. Add 2000 to the year portion. More
complex add 1900 or 2000 depending on what you decide is the cutover date
1

NewDate=DateSerial(CInt(Right([JulianDate],2)) +
IIF Cint(right([JulianDate],2) < 30,2000,1900),
Mid([JulianDate],3,2),Left([JulianDate],2))

You can try the simpler

DateValue(Format(JulianDate,"@@\/@@\/@@"))

That should work to give you a valid date assuming that your date settings are
mm/dd/yy in your OS.

TEST TEST TEST on a COPY. If it works then use it.