Convert date using vbs

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@
LVL 12
mattclarifiedAsked:
Who is Participating?
 
omgangConnect With a Mentor IT ManagerCommented:
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
 
omgangIT ManagerCommented:
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
 
Rgonzo1971Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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
 
Rgonzo1971Commented:
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
 
mattclarifiedAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.