Solved

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

Posted on 2015-02-09
27
240 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
[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
  • 10
  • 7
  • 6
  • +1
27 Comments
 
LVL 11

Expert Comment

by:John Easton
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
Technology Partners: 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!

 
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
 
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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
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…

728 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