Converting Dates from YYMMDD to MMDDYYYY

[Uni]

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!

 

[Douglas J. Steele]

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.

 

[Uni]

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

 

[Douglas J. Steele]

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;


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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:

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.

 

[Van T. Dinh]

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".