Solved

Change order of a date string from dd.mm.yy to yy.mm.dd in VB Script

Posted on 2015-02-09
27
224 Views
Last Modified: 2016-02-11
I have a function that a date time string is passed into and then
2 validation checks and a replace is done.

The date time is passed in as dd.mm.yy hh:ss:min

I would like to be able to show the date as yy/mm/dd (swap the order of
date to start with year first then month then day)

How can i do that? Tired strReverse but that does exactly what it says!

Heres the code snip

Private Function DateOrTime(strText)
   Dim x
   DateOrTime = True    
   
 'Validate the characters in the date string
   If Len (strText) = 8 Then
     For x = 1 To 8
      If x <> 3 And x <> 6 Then
       If InStr("0123456789",Mid(strText,x,1)) = 0 Then
         DateOrTime = False
End If
Exit For
'Extra validation to check time
      Else
       If InStr(":.",Mid(strText,x,1)) = 0 Then
         DateOrTime = False              
          Exit For
          End If                  
            
     End If    
     Next
   End If  
   'Search for '.' and change to '-' in string
   strText =Replace(strText, ".", "-")  
    End Function
0
Comment
Question by:wilko1000
  • 10
  • 7
  • 6
  • +1
27 Comments
 
LVL 10

Expert Comment

by:JEaston
ID: 40598378
Assuming it is a string, then you should be able to use Left, Right and Mid.  Therefore your string is currently something like 01.10.15 10:20:20.

Therefore:
strText = mid(strText,7,2) & "/" & mid(strText,4,2) & "/" & left(strText,2)

Open in new window


This is from memory, but I think the numbers are the right way round.  It also assumes the input string will ALWAYS be formatted the same way.
0
 
LVL 3

Assisted Solution

by:Onisan
Onisan earned 500 total points
ID: 40598446
Here you go, a small function that you pass your string to and it will return it formatted
also a test sub to check it works.


Sub Test()
Dim Mystr As String
   
    Mystr = ChangeDate("01.02.10 13:12:05")

    MsgBox Mystr


End Sub

Function ChangeDate(dte As String) As String

Dim myVar() As String

    myVar = Split(Replace(dte, " ", "."), ".")
   
    ChangeDate = myVar(2) & "/" & myVar(1) & "/" & myVar(0)
   
End Function
0
 

Author Comment

by:wilko1000
ID: 40598697
@jeaston

Your solution looks very neat however its returned

13/07/04 14/29/12

The date is now in perfect order but its also swapped the time around too

It needs to be 13/07/04 12:29:14
0
 
LVL 3

Expert Comment

by:Onisan
ID: 40598766
My apologies,
Please try the below

Sub Test()

Dim Mystr As String
   
    Mystr = ChangeDate("01.02.10 13:12:05")

    MsgBox Mystr


End Sub

Function ChangeDate(dte As String) As String

Dim myVar() As String

    myVar = Split(Left(dte, 8), ".")
    ChangeDate = myVar(2) & "/" & myVar(1) & "/" & myVar(0) & Right(dte, 9)
   
End Function
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40598931
if you are being passed a date or datetime string, why do you need to validate it?
0
 

Author Comment

by:wilko1000
ID: 40599007
Hi aikimark
I dont want to validate it, i want to change the order of the date so it can be accepted bhy an external interface as that interface will not except any but yy-mm-dd

This nearly works

strText = mid(strText,7,2) & "/" & mid(strText,4,2) & "/" & left(strText,2)

But its also sawpping the time order too.

I suspect i will need to split the date and time then just order the date
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40599038
that interface will not except any but yy-mm-dd
Then why are you using a slash character separator instead of a dash separator?
0
 

Author Comment

by:wilko1000
ID: 40599061
I  have changed that to '-'

Now reads

strText = mid(strText,7,2) & "-" & mid(strText,4,2) & "-" & left(strText,2)
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40599069
do you now have the results you need?
0
 

Author Comment

by:wilko1000
ID: 40599123
No not quite

Thsi lone was kindly submitted which nearly sorts the problem:
strText = mid(strText,7,2) & "-" & mid(strText,4,2) & "-" & left(strText,2)

But whats happening is its changing this

 dd.mm.yy hh:ss:min

to this

 yy-mm-dd ss-min-hr (note the time has been reversed too)

I only want to change the date bit so it looks like this:

yy-mm-dd hr-min-ss (keeping the time the same but just changing the date bit)
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40599178
Public Function DateOrTime(ByVal parmDateString As String) As String
    'converts parameter date string in "dd.mm.yy hh:ss:min" format
    'to 'yy-mm-dd hh:ss:min" format
    Dim strTemp As String
    Dim strDTparsed() As String
    strDTparsed = Split(parmDateString, " ")
    strTemp = strDTparsed(0)
    strTemp = Mid$(strTemp, 7, 2) & "/" & Mid$(strTemp, 4, 2) & "/" & Mid$(strTemp, 1, 2)
    DateOrTime = strTemp & " " & strDTparsed(1)
End Function

Open in new window

0
 

Author Comment

by:wilko1000
ID: 40599340
I get an error with that:


Line 159
Char: 51
Error: Expected ')'
Coce: 800A03EE

The Line 159 is the following line:
Private Function DateOrTime(ByVal parmDateString As String) As String
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!

 
LVL 45

Expert Comment

by:aikimark
ID: 40599407
My routine is only 10 lines long.  I'm not sure where the error is.

I just noticed that this is in the VBScript zone.  Use this instead:
Public Function DateOrTime(ByVal parmDateString)
    'converts parameter date string in "dd.mm.yy hh:ss:min" format
    'to 'yy-mm-dd hh:ss:min" format
    Dim strTemp
    Dim strDTparsed
    strDTparsed = Split(parmDateString, " ")
    strTemp = strDTparsed(0)
    strTemp = Mid(strTemp, 7, 2) & "/" & Mid(strTemp, 4, 2) & "/" & Mid(strTemp, 1, 2)
    DateOrTime = strTemp & " " & strDTparsed(1)
End Function

Open in new window

0
 
LVL 3

Expert Comment

by:Onisan
ID: 40600170
I've given you a working function that does what you've asked but you don't seem to have even acknowledged it
Here is it again.

Function ChangeDate(dte As String) As String
Dim myVar() As String
    myVar = Split(Left(dte, 8), ".")
    ChangeDate = myVar(2) & "/" & myVar(1) & "/" & myVar(0) & Right(dte, 9)
End Function

And to test it


Sub Test()
    MsgBox ChangeDate("01.02.10 13:12:05")
End Sub
0
 

Author Comment

by:wilko1000
ID: 40600255
Hi Onisan

I get na error with your code,are those lines for VBA, Im using VBS

The error is:

Line: 159
Char: 34
Error Expected ')'

The line 159 is Private Function DateOrTime(dte As String) As String
0
 

Author Comment

by:wilko1000
ID: 40600257
Hi Aikimark

Your staement produces and error

Line: 167
Car: 5
Error Subscript out of range: [Number:1]

That line is
DateOrTime = strTemp & " " & strDTparsed(1)

The line being passed in 04.07.13   12:29:14
0
 
LVL 3

Expert Comment

by:Onisan
ID: 40600265
In VB Script this is all you need

Dim TheString
                TheString = "01.02.10 13:12:05"
                msgbox Mid(TheString,7,2) & "/" & Mid(TheString,4,2) & "/"  & Left(TheString,2) & Right(TheString,9)
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40600519
The duplicate space characters messed it up.  This version of the code removes them.
Public Function DateOrTime(ByVal parmDateString)
    'converts parameter date string in "dd.mm.yy hh:ss:min" format
    'to 'yy-mm-dd hh:ss:min" format
    Dim strTemp
    Dim strDTparsed
    strTemp = Trim(parmDateString)
    Do Until InStr(strTemp, "  ") = 0       'remove duplicate internal spaces
        strTemp = Replace(strTemp, "  ", " ")
    Loop
    strDTparsed = Split(strTemp, " ")
    strTemp = strDTparsed(0)
    strTemp = Mid(strTemp, 7, 2) & "/" & Mid(strTemp, 4, 2) & "/" & Mid(strTemp, 1, 2)
    DateOrTime = strTemp & " " & strDTparsed(1)
End Function

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 40600526
I tested the latest version of Onisan's code and it works.  You only need to put that (Mid & Right) statement into a function in your VBScript.
0
 

Author Comment

by:wilko1000
ID: 40605465
Worked perfectly, thanks
0
 
LVL 3

Expert Comment

by:Onisan
ID: 40605518
Which I gave :-(
0
 

Author Comment

by:wilko1000
ID: 40606036
Hi Onisan

Sorry mate,  JEaston came up with it first

Cheers
0
 
LVL 3

Accepted Solution

by:
Onisan earned 500 total points
ID: 40606066
so why carry on saying it wasn't right and expecting more solutions instead of accepting it?

I then gave you a perfectly working two line piece of code which did exactly what you expected including your time at the end

Aikmark tested and said it worked in VBScript and you then said
Works perfectly.

So basically after giving you the EXACT solution to your question you said thanks and accepted a solution that didn't do what you asked.
This is why spending time on here sometimes jut doesn't seem worth it.
0
 

Author Closing Comment

by:wilko1000
ID: 40607775
Split the points which on refection is fairer. Thanks for all the feedback, all responses are invaluable
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

758 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

22 Experts available now in Live!

Get 1:1 Help Now