Converting integers into dates

[Ajrbruce]

I have a large database where the Date of birth has been set to type integer
of the form dd/mm/yy. I need to perform some calculations on the dates but
when I try to just change the type to date it seems to come up with a totally
different date.

Can anyone suggest how these can be converted and still keep the correct
values?

Thanks

 

[Douglas J. Steele]

How are you attempting to convert the integers, and how are you attempting
to use the resultant dates?

If you're trying to use the dates in SQL statements (which includes the
Domain Aggregate functions such as DLookup), regardless of what your Short
Date format has been set to through Regional Settings, you must use
mm/dd/yyyy format (or else an unambiguous one such as yyyy-mm-dd or dd mmm
yyyy)

You may want to read Allen Browne's "International Dates in Access" at
http://www.allenbrowne.com/ser-36.html, or what I had in my September, 2003
"Access Answers" column in Pinnacle Publication's "Smart Access". (You can
download the column, and sample database, for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html)

 

[Dirk Goldgar]

In

I have a large database where the Date of birth has been set to type
integer of the form dd/mm/yy.

Integer? Not Long Integer? An Integer field in Access/Jet can't hold
6-digit numbers. Unless this database is in some other file format, I'm
going to assume you mean Long Integer.

I need to perform some calculations on
the dates but when I try to just change the type to date it seems to
come up with a totally different date.

Can anyone suggest how these can be converted and still keep the
correct values?

Do you mean that today's date, for example, would be stored as 290507?
If so, you might try an expression like

CDate(Mid(CStr([DOB]), 3, 2) & "/" &
Mid(CStr([DOB]), 1, 2) & "/" &
Mid(CStr([DOB]), 5, 2))

where DOB is the name of the field.

 

[Douglas J. Steele]

In

I have a large database where the Date of birth has been set to type
integer of the form dd/mm/yy.

Integer? Not Long Integer? An Integer field in Access/Jet can't hold
6-digit numbers. Unless this database is in some other file format, I'm
going to assume you mean Long Integer.

I need to perform some calculations on
the dates but when I try to just change the type to date it seems to
come up with a totally different date.

Can anyone suggest how these can be converted and still keep the
correct values?

Do you mean that today's date, for example, would be stored as 290507? If
so, you might try an expression like

CDate(Mid(CStr([DOB]), 3, 2) & "/" &
Mid(CStr([DOB]), 1, 2) & "/" &
Mid(CStr([DOB]), 5, 2))

where DOB is the name of the field.

Since the OP is wanting to use dd/mm/yy, odds are that's the Short Date
format on the machine (as set through Regional Settings).

That means that while

CDate(Mid(CStr([DOB]), 3, 2) & "/" &
Mid(CStr([DOB]), 1, 2) & "/" &
Mid(CStr([DOB]), 5, 2))

will work fine for a value of 290507 for DOB, it will result in 06 Jan,
2007, not 01 Jun, 2007, when the value for DOB is 010607.

Far safer is to use the DateSerial function. However, that does require a 4
digit year, so some logic will have to be used to determine whether the year
should be in the 1900s or the 2000s. If we're dealing with DOB, it's likely
legimate to use:

DateSerial(IIf(Mid(CStr([DOB]), 5, 2) > CStr(Year(Date())), CLng("19" &
Mid(CStr([DOB]), 5, 2)), CLng("20" & Mid(CStr([DOB]), 5, 2))),
CLng(Mid(CStr([DOB]), 3, 2)), CLng(Mid(CStr([DOB]), 1, 2))

 

[John W. Vinson]

Far safer is to use the DateSerial function. However, that does require a 4
digit year, so some logic will have to be used to determine whether the year
should be in the 1900s or the 2000s. If we're dealing with DOB, it's likely
legimate to use:

DateSerial(IIf(Mid(CStr([DOB]), 5, 2) > CStr(Year(Date())), CLng("19" &
Mid(CStr([DOB]), 5, 2)), CLng("20" & Mid(CStr([DOB]), 5, 2))),
CLng(Mid(CStr([DOB]), 3, 2)), CLng(Mid(CStr([DOB]), 1, 2))

I was curious so I checked: I knew DateSerial was a clever bit of code, but
it's even cleverer than I thought!

?dateserial(7,5,29)
5/29/2007
?dateserial(29,1,1)
1/1/2029
?dateserial(30,1,1)
1/1/1930

so it uses the familiar two/four digit convention.

Dates of birth *really* need to be four digit. There are lots of centenarians
around now, and being born in '05 (or even in '98) is still ambiguous!

John W. Vinson [MVP]

 

[Douglas J. Steele]

Far safer is to use the DateSerial function. However, that does require a
4
digit year, so some logic will have to be used to determine whether the
year
should be in the 1900s or the 2000s. If we're dealing with DOB, it's
likely
legimate to use:

DateSerial(IIf(Mid(CStr([DOB]), 5, 2) > CStr(Year(Date())), CLng("19" &
Mid(CStr([DOB]), 5, 2)), CLng("20" & Mid(CStr([DOB]), 5, 2))),
CLng(Mid(CStr([DOB]), 3, 2)), CLng(Mid(CStr([DOB]), 1, 2))

I was curious so I checked: I knew DateSerial was a clever bit of code,
but
it's even cleverer than I thought!

?dateserial(7,5,29)
5/29/2007
?dateserial(29,1,1)
1/1/2029
?dateserial(30,1,1)
1/1/1930

so it uses the familiar two/four digit convention.

Actually, it makes sense that it would use whatever's defined on the Date
tab under Customer Regional Options for the "When a two-digit year is
entered, interpret it as a year between",

Dates of birth *really* need to be four digit. There are lots of
centenarians
around now, and being born in '05 (or even in '98) is still ambiguous!

No argument from me on this point!

 

[Dirk Goldgar]

In

Since the OP is wanting to use dd/mm/yy, odds are that's the Short
Date format on the machine (as set through Regional Settings).

Good point, Doug. I forgot that CDate would use the regional settings.
I was thinking it was like Jet's interpretation of date literals.