Solved

Convert date using vbs

Posted on 2013-12-12
6
321 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
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 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

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…
Welcome back!  My apologies for taking so long to write part two of this series; it's been a long time coming!  As I promised in Part 1, this article will focus on how to locate those elusive AD properties that you are searching for.  Why is this us…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

707 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