VBScript - Need to verify a date and add leading zeros if needed

This needs to be done in VBScript so I can use it in an ASP page

I have a database that  gets a little wonky due to so many people acessing it.  Before I run a specific report I need to check the database and replace any missing leading zeros.

For example all dates should be formatted like:

01/07/11
and NOT
1/07/11
OR
01/7/11

However, the data is actually formatted as text and not "dates" .
(That should make it even easier)


The trouble is that I have worked on this for a while and can't come up with a working solution, it's like I have a block in my brain or something, I know it's simple....and it's killing me.

I am currently using a SQL statement to select out records with a length of 7 and adding a leading zero and updating in a loop and it works for most but that doesn't work for 01/7/11 obviously as it makes it 001/7/11

I have to select out all records that are <7 and then do a check to see where the missing zero(s) is/are

EGormlyAsked:
Who is Participating?
 
prashanthdCommented:
Try the following code.

regards
Prashanth
dt="1/7/11"

If Len(Month(dt))<2 Then
    strmonth="0" & Month(dt)
Else
    strmonth=Month(dt)
End If
If Len(Day(dt))<2 Then
    strday="0" & Day(dt)
Else
    strday=Day(dt)
End If
stryear=Year(dt)

newdt= strmonth &"/"& strday &"/"& Mid(stryear,3)

WScript.Echo newdt

Open in new window

0
 
Om PrakashCommented:
in VBScript, you can split the date by "/" and append zero

Example:
right("00" & 7, DayField)  - will give you 07

and join the string again for date
0
 
EGormlyAuthor Commented:
I have no idea what you mean :(

That doesn't look like a valid "right()" to me.
From what I understand right() = right(variable,number of digits)

dayfield would be the start of the day in a date field which I do not have (it's text) and I would need the end No?  And  "00" & 7 would give an error. No?

Thanks for helping but I just don't get it.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
EGormlyAuthor Commented:
Thank you!
0
 
EGormlyAuthor Commented:
excellent, thanks for teaching me today!
0
 
prashanthdCommented:
Thanks for the points..

regards
Prashanth
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.