Currently I have a data source that stores dates in the format yyyymmdd, and I need to return them in the format mm/dd/yyyy (there will not be a time portion to either date). I have no problem retrieving the dates from the date source, but the following is not reformatting the date (but it doesn't error either)
Set LookupDate = FormatDateTime(objRS.Fields("Date"),2)
Any help would be great!
Thanks everyone for the input! I used a combo of elements to get a working final answer, here it is:
PUDate = objRS.Fields("Date")
YearPart = Left(PUDate, 4)
MonthPart = Mid(PUDate, 5, 2)
DayPart = Right(PUDate, 2)
LookupDate = MonthPart & "/" & DayPart & "/" & YearPart
FormatDateTime() returns a string, not an object; so get rid of the "Set". If that does not deliver the intended result, say so.
FormatDateTime takes into account the locale of the system.
I would use the following approach:
dim rawDate
rawDate = cDate(objRS.Fields("Date"))
dim lookupDate
lookupDate = month(rawDate)
lookupDate = lookupDate & "/" & day(rawDate)
lookupDate = lookupDate & "/" & year(rawDate)
If you want to include leading zero's:
dim rawDate, rawDay, rawMonth
rawDate = cDate(objRS.Fields("Date"))
rawDay = day(rawDate)
if rawDay < 10 then
rawDay = "0" & rawDay
end if
rawMonth = month(rawDate)
if rawMonth< 10 then
rawMonth = "0" & rawMonth
end if
dim lookupDate
lookupDate = rawMonth
lookupDate = lookupDate & "/" & rawDay
lookupDate = lookupDate & "/" & year(rawDate)
month , date left pad zero:
right("0" & month(dt), 2) & "/" & right("0" & day(dt), 2) & "/" & year(dt)
In case you need date and time, you can use the following function. This will give you date and time formatted as YYYYMMDDhhmmss
function GetFormattedDateTime
dim t
t = now
GetFormattedDateTime= DatePart("yyyy",t) & _
Right("0" & DatePart("m",t), 2) & _
Right("0" & DatePart("d",t), 2) & _
Right("0" & DatePart("h",t), 2) & _
Right("0" & DatePart("n",t), 2) & _
Right("0" & DatePart("s",t), 2)
end function
mm/dd/yyyy
not yyyymmddhhnnss
- Ekkehard.Horner 2015-01-08 09:23
You'll need to add hyphens to your input; it can then be passed through. Here's one method:
Dim d
d = objRS.Fields("Date")
d = Left(d, 4) & "-" & Mid(d, 5, 2) & "-" & Right(d, 2)
LookupDate = FormatDateTime(d, 2)
FormatDateTime(d, 2)
doesn't format the date as mm/dd/yyyy
; it formats it in the system's short date format. For US locales, this is mm/dd/yyyy
; for the UK and related locales, it is usually dd/mm/yyyy
- KyleNZ 2012-04-04 21:08
yyyymmdd
oryyyy-mm-dd
format?mm/dd/yyyy
is ambiguous; it's difficult to tell whether01/02/2012
is January 2 or February 1.yyyy-mm-dd
is an international standard, it's unambiguous, and it sorts nicely - Keith Thompson 2012-04-04 22:16