Solved

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

Posted on 2011-03-18
6
1,001 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This video discusses moving either the default database or any database to a new volume.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now