Solved

Convert date using vbs

Posted on 2013-12-12
6
320 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
[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
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 51

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
Industry Leaders: 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 53

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 51

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

Industry Leaders: 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!

Question has a verified solution.

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

Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
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…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

732 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