Friday, August 11, 2006

VBScript: Add Leading Zero to Date Function Output

When you generate reports/files using VBScript periodically, it is always a good idea to suffix the file name with date/time components - e.g., "REPORT_20060811" - as it makes files easier to locate because they would already be sorted.

Using functions Year( Date ), Month( Date ), Day( Date ), subcomponents can be extracted to create filename suffix. You can also use DatePart( datetype,date ) function to extract the same components. But the problem with using any of these functions as they are, is that you would not get proper sorting orders because these will output single digits for numbers less than 10. For example, the following two lines of code will generate date components which will not have proper sorting order

strDate = Year(Date) & Month(Date) & Day(Date)
OR
strDate = DatePart("yyyy",Date) _
        & DatePart("m",Date) _
        & DatePart("d",Date)

To generate the string which will have the right sorting order, you need to append leading zeros to entries less than 10. That is what the following line of code does. It appends leading zero to all the entities, extracts 2 characters from right, and builds the string.

strDate = DatePart("yyyy",Date) _
        &
Right("0" & DatePart("m",Date), 2) _
        &
Right("0" & DatePart("d",Date), 2)

28 comments:

Anonymous said...

Elegant! Only other function I found to do the exact same thing was about 30 lines of code. Thanks.

Anonymous said...

Wow, this is awesomely simple and just what I needed in my script. Thanks!

DP in SF said...

Absolutely beautiful. I needed the same thing. Either the dates are wrong on the two comments above, or for some nutty reason the last two days have generated a number of people needing and appreciating your post over 2 years ago!

T. Nickey said...

Thank you so much. I looked high and low for example code on how to save a date in reverse and this works perfect.

Anonymous said...

Thanks, I used this to get leading zeros in a timestamp as well:

strTime = Right("0" & Hour(Now), 2) & ":" & Right("0" & Minute(Now), 2) & ":" & Right("0" & Second(Now), 2)

mbelow said...
This comment has been removed by the author.
Anonymous said...

I have to go back through years of code and remove my clunky AddLeadingZero() functions. Thanks for posting this!

paolo said...

!bravis! this is great

Anonymous said...

Amazingly simple yet perfect for what I need.

Grozny said...

strDate = Format(Date, "yyyymmdd")

Will do the same. Looks shorter to me.

See also
http://msdn.microsoft.com/en-us/library/73ctwf33(VS.80).aspx

Raj said...

Format function doesn't work in VBScript, works only in Visual Basic.
http://www.4guysfromrolla.com/webtech/051601-1.shtml

irontomsk said...

Very handy! Thank you very much.

Anonymous said...

Thanks!!!

Anonymous said...

Cheers, Just wat was needed.

DermottB said...

Wow, beautiful code, ver nice!

drofmij said...

Excellent!

I just used this code snippet to cleanup a batch file that used dated file names. Very helpful and much cleaner.

~drofmij

Anonymous said...

Great! Thank you so much!

Theo Macris said...

Just what I needed. Thanks!

Anonymous said...

Perfect! Just what I needed for my RoboCopy script.

Anonymous said...

very clever : )

Anonymous said...

Nicely done! I've been looking all over the place for a short elegant solution... this is the only one!

Thanks!

Anonymous said...

Thankyou..was really helpful

Anonymous said...

THANKS!!!

Anonymous said...

Absolutly brilliant solution. Why did I never think of that.
From know on, no more hasseling with silly if statements :-)

Cheers!

Anonymous said...

Thanks..it is helpful :)

Anonymous said...

Wonderful.. Thanks :)

Anonymous said...

Perfect. Works both in straight code and within an MS Access query. Thanks!

Anonymous said...

Excellent! Works great, thanks a lot. :)