Thursday, November 6, 2008

Add Leading Zero to Date Function Output

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)


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)

No comments: