[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2011-03-18
6
Medium Priority
?
1,070 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

649 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