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

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
wilko1000Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
OnisanConnect With a Mentor Commented:
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
 
John EastonDirectorCommented:
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
 
OnisanConnect With a Mentor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
wilko1000Author Commented:
@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
 
OnisanCommented:
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
 
aikimarkCommented:
if you are being passed a date or datetime string, why do you need to validate it?
0
 
wilko1000Author Commented:
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
 
aikimarkCommented:
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
 
wilko1000Author Commented:
I  have changed that to '-'

Now reads

strText = mid(strText,7,2) & "-" & mid(strText,4,2) & "-" & left(strText,2)
0
 
aikimarkCommented:
do you now have the results you need?
0
 
wilko1000Author Commented:
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
 
aikimarkCommented:
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
 
wilko1000Author Commented:
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
 
aikimarkCommented:
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
 
OnisanCommented:
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
 
wilko1000Author Commented:
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
 
wilko1000Author Commented:
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
 
OnisanCommented:
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
 
aikimarkCommented:
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
 
aikimarkCommented:
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
 
wilko1000Author Commented:
Worked perfectly, thanks
0
 
OnisanCommented:
Which I gave :-(
0
 
wilko1000Author Commented:
Hi Onisan

Sorry mate,  JEaston came up with it first

Cheers
0
 
wilko1000Author Commented:
Split the points which on refection is fairer. Thanks for all the feedback, all responses are invaluable
0
All Courses

From novice to tech pro — start learning today.