Power By 专否社区

How can I format a date from yyymmdd to mm/dd/yyyy using vbscript?

Go To StackoverFlow.com

2

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!

2012-04-04 20:28
by Xedicon
Would you consider leaving it in yyyymmdd or yyyy-mm-dd format? mm/dd/yyyy is ambiguous; it's difficult to tell whether 01/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

0

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
2012-04-06 13:53
by Xedicon

1

FormatDateTime() returns a string, not an object; so get rid of the "Set". If that does not deliver the intended result, say so.

2012-04-04 20:49
by Ekkehard.Horner

1

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)
2012-04-05 07:13
by Guido Gautier

1

month , date left pad zero:

right("0" & month(dt), 2) & "/" & right("0" & day(dt), 2) & "/" & year(dt)

2013-12-08 12:30
by raidsan

1

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
2015-01-08 08:55
by Max
(1) You shouldn't use the volatile Now() function more than once in an expression. (2) The desired format is mm/dd/yyyy not yyyymmddhhnnss - Ekkehard.Horner 2015-01-08 09:23
@Ekkehard.Horner thanks for the suggestion about Now(), I fixed the code. I know my answer is not the correct one for the asked question ... but the fact is that I was looking for a vbscript to format date/time as yyyymmddhhmmss, and this question is one of the first link that Google give me, so I thought this could be a good place to post a more generic answer, that wont help the question opener, but may help all people that happen to open this link while looking to something similar to "format a date as mm/dd/yyyy - Max 2015-01-08 10:10

0

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)
2012-04-04 20:56
by KyleNZ
One minor point: 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
Thanks for the pointer on FormatDateTime being locale based, I was unaware - Xedicon 2012-04-05 20:09
Ads