[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1076
  • Last Modified:

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

0
EGormly
Asked:
EGormly
  • 3
  • 2
1 Solution
 
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
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

regards
Prashanth
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now