?
Solved

Convert date using vbs

Posted on 2013-12-12
6
Medium Priority
?
336 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 54

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 2000 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
Independent Software Vendors: 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 54

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 54

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Script to copy or move mouse-selected collection of files plus targets referenced by shortcuts (.lnk) The purpose of this article is to help illuminate the real challenges and options available (where they may exist) for utilizing simple scriptin…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month14 days, 11 hours left to enroll

839 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