Solved

Convert date using vbs

Posted on 2013-12-12
6
318 Views
Last Modified: 2013-12-13
Hi,

I have syslog data being parsed in SCOM to a text file using a vbs script which is then used to import into an SQL database. The date field that comes out of the syslog is not acceptable by SQL standards.

I am looking for a way in vbs to convert a date field from "Dec 12 14:30:25" to "2013-12-12 14:30:25"

I am happy to use year(now) to give me the current year, but need to know how I would feed it all into a variable that I can use.

Thanks
M@
0
Comment
Question by:mattclarified
6 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 39714166
Split function will allow you to split the string and then you can use Select Case to convert the Month to a number value

example

Dim strDate
Dim arDate
Dim i

strDate = "Dec 12 14:30:25"
arDate = Split(strDate, " ")

For i = 0 to UBound(arDate)
      Wscript.Echo arDate(i)
Next

WScript.Quit


OM Gang
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39714170
Hi,

pls try

Sub Macro3()
myDate = "Nov 12 14:30:25"
findYrPlace = InStrRev(myDate, " ")
dateWithYr = Left(myDate, findYrPlace - 1) & ", " & Year(Now()) & " " & Right(myDate, findYrPlace + 1)
d = CDate(dateWithYr)
End Sub

Open in new window

Regards
0
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 39714175
Try,

Dim strDate, strSQLDate, strMonth
Dim arDate
Dim i

strDate = "Dec 12 14:30:25"
arDate = Split(strDate, " ")

'For i = 0 to UBound(arDate)
'      Wscript.Echo arDate(i)
'Next

Select Case arDate(0)
    Case "Jan"
        strMonth = "01"

    Case "Feb"
        strMonth = "02"

etc.

End Select

strSQLDate = "20" & arDate(1) & "-" & strMonth & "-" & arDate(2)

WScript.Echo strSQLDate

WScript.Quit


OM Gang
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39714176
MyNewDate=convertDate("Dec 12 14:30:25")
function convertDate(d)
    arrD=split(d," ")
    monthName=arrD(0)
    theDay=arrD(1)
    theTime=arrD(2)
   
   convertDate=year(date)&"-"&whatMonth(monthName)&"-"&theDay&" "&theTime

end function
function whatMonth(m)
   select case m
       case "Jan" 
           whatMonth="01"
       case "Feb" 
           whatMonth="02"
       case "Mar"
           whatMonth="03" 
       case "Apr" 
           whatMonth="04"
       case "May" 
           whatMonth="05"
       case "Jun" 
           whatMonth="06"
       case "Jul" 
           whatMonth="07"
       case "Aug" 
           whatMonth="08"
       case "Sep" 
           whatMonth="09"
       case "Oct" 
           whatMonth="10"
       case "Nov" 
           whatMonth="11"
       case "Dec" 
           whatMonth="12"

   end select
end function

Open in new window

0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39714361
Hi,
Corrected code

Sub Macro3()
myDate = "Nov 12 14:30:25"
findYrPlace = InStrRev(myDate, " ")
dateWithYr = Left(myDate, findYrPlace - 1) & ", " & Year(Now()) & Mid(myDate, findYrPlace)
d = CDate(dateWithYr)
End Sub

Open in new window

Regards
0
 
LVL 12

Author Closing Comment

by:mattclarified
ID: 39716301
Needed a couple of little tweaks to output the string as required, but overall a great solution and just what I was looking for.
Many thanks!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Well hello again!  Glad to see you've made it this far without giving up.  In this, the fourth installment of my popular series, I'm going to cover functions and subroutines, what they are, and why they are useful.  Just in case you stumbled onto th…
This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

820 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