Solved

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

Posted on 2011-03-18
6
1,035 Views
Last Modified: 2012-06-21
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
Comment
Question by:EGormly
  • 3
  • 2
6 Comments
 
LVL 22

Expert Comment

by:Om Prakash
ID: 35165175
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
 
LVL 12

Accepted Solution

by:
prashanthd earned 500 total points
ID: 35165227
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
 

Author Comment

by:EGormly
ID: 35165235
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:EGormly
ID: 35165680
Thank you!
0
 

Author Closing Comment

by:EGormly
ID: 35165683
excellent, thanks for teaching me today!
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35165707
Thanks for the points..

regards
Prashanth
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Html fieldset fix its height and width 4 44
Validating Date 4 29
Session on Html 8 41
Display field if column exists 7 35
Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question