Converting Dates from YYMMDD to MMDDYYYY

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Uni - 24 Sep 2006 11:39 GMT

Hi All;

I'm looking for a format function that will convert a date in the text
format of YYMMDD to text format of MMDDYYYY.

Any help would be GREATLY appreciated!

Thanks in advance!

Reply to this Message

Douglas J. Steele - 24 Sep 2006 12:17 GMT

You can't do it using a Format function, but try:

Function SwitchDateFormat(YYMMDD As String) As String
Dim strDay As String
Dim strMonth As String
Dim strYear As String

If Len(YYMMDD) = 6 Then
strYear = Left$(YYMMDD, 2)
strMonth = Mid$(YYMMDD, 3, 2)
strDay = Right$(YYMMDD, 2)

If strYear < "30" Then
strYear = "19" & strYear
Else
strYear = "20" & strYear
End If

SwitchDateFormat = strYear & strMonth & strDay
End If

End Function

Note that I'm assuming any dates with a year of 00 to 29 are supposed to be
2000 to 2029, and that any dates with a year of 30 to 99 are supposed to be
1930 to 1999. Change the If statement if your situation is different.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hi All;
>
[quoted text clipped - 4 lines]
>
> Thanks in advance!

Reply to this Message

Uni - 24 Sep 2006 13:33 GMT

Thanks, Douglas

I was using a query and came up with:

SELECT [REF-TYPE] & [REF-SERIAL-NBR] AS Jon, Mid$([OPEN-DT],3,2) &
Right$([OPEN-DT],2) & Left$([OPEN-DT],2) AS ConvertOpenDt, P052.[OPEN-DT],
P052.[CLOSE-DT], Mid$([CLOSE-DT],3,2) & Right$([CLOSE-DT],2) &
Left$([CLOSE-DT],2) AS ConvertCloseDt
FROM P052;

But now my problem seems to be that I can't find a way to convert the MMDDYY
to MMDDYYYY. Looks like I will have to learn to write modules.

Many thanks!

Uni

> You can't do it using a Format function, but try:
>
[quoted text clipped - 32 lines]
>>
>> Thanks in advance!

Reply to this Message

Douglas J. Steele - 24 Sep 2006 20:43 GMT

You really would be better off, I think, using a function rather than doing
it in code, but...

SELECT [REF-TYPE] & [REF-SERIAL-NBR] AS Jon, Mid$([OPEN-DT],3,2) &
Right$([OPEN-DT],2) & IIf(Left$([OPEN-DT],2) < "30", "20" &
Left$([OPEN-DT],2), "19" & Left$([OPEN-DT],2))
AS ConvertOpenDt, P052.[OPEN-DT], P052.[CLOSE-DT], Mid$([CLOSE-DT],3,2) &
Right$([CLOSE-DT],2) &
IIf(Left$([CLOSE-DT],2) < "30", "20" & Left$([CLOSE-DT],2), "19" &
Left$([CLOSE-DT],2)) AS ConvertCloseDt
FROM P052;

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Thanks, Douglas
>
[quoted text clipped - 49 lines]
>>>
>>> Thanks in advance!

Reply to this Message

Van T. Dinh - 01 Oct 2006 12:45 GMT

If you date values are in the "default" century (set in Windows OS), you can
use the expression:

Format( DateSerial( Left([OPEN-DT], 2),
Mid([OPEN-DT], 3, 2),
Right([OPEN-DT,2) ), "mmddyyyy" )

to convert the date value stored as string in "yymmdd" to a date in the
format "mmddyyyy".

Signature

HTH
Van T. Dinh
MVP (Access)

> Thanks, Douglas
>
[quoted text clipped - 12 lines]
>
> Uni

Reply to this Message