asked on

VBSCRIPT Returning value 1.6.40 as 1/6/1940

this vb script is returning the value in the column as dates and it shouldn't

im using the line 34
objRecordset.Open " SELECT ACT_ID, FORMAT([INTRO_DATE],'mm/dd/yyyy') as [INTRO_DATE] FROM " & objFile.Name, objConnection

and my textpad is returning 1/6/1940 01/07/2010 01/07/2010
when it should be returning 1.6.40 01/07/2010 01/07/2010

the date format for the date has to remain becuase it is needed for the next phase of the application. and without it the dates do not return correctly.

Attached is the script
sample data
and result

can be execute by command
cscript Script.vbs sample.csv 1.txt

Thank you in advance
sample.csv
Script.vbs
1.txt

sirbounty

Will this work for you?

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
Const adDate = 7
Const adVarChar = 200
Const MaxCharacters = 255 
 
' Get input file name from command line parm
If (WScript.Arguments.Count > 0) Then
  strInFile = WScript.Arguments(0)
Else
  WScript.Echo "No input filename specified."
  WScript.Quit
End If

' Get output file name from command line parm
If (WScript.Arguments.Count > 1) Then
  strOutFile = WScript.Arguments(1)
Else
  WScript.Echo "No output filename specified."
  WScript.Quit
End If

' Create needed objects
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set rsData = CreateObject("ADOR.Recordset")

rsData.Fields.Append "ACT_ID", adVarChar, MaxCharacters
rsData.Fields.Append "INTRO_DATE", adDate
rsData.Open

Set objFile = objFSO.OpenTextFile   (strInFile, ForReading)

objFile.SkipLine 'skip header

Do Until objFile.AtEndOfStream
    arrData = Split(objFile.ReadLine, ",")
    strIntroDate = arrData(0)
    strActID = arrData(1)

    rsData.AddNew
    rsData(0) = strActID
    rsData(1) = strIntroDate
    rsData.Update
Loop

objFile.Close

rsData.MoveFirst

Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objFile.WriteLine "ACT_ID" & vbTab & "INTRO_DATE" & vbTab & "INTRO_DATE"

Do Until rsData.EOF
    objFile.WriteLine rsData.Fields.Item(0) & vbTab & rsData.Fields.Item(1) & vbTab & rsData.Fields.Item(1) 
    rsData.MoveNext
Loop

objFile.Close
wscript.quit

Open in new window

plusone3055

ASKER

got the error expected end of statment

plusone3055

ASKER

i copied and pasted everything into a blank
script.vbs and got
Script.vbs(39,5) VbScript Runtime error Subscript Out of range [Number: 0]

sirbounty

Are you using a different sample file than the one you posted? I get no errors...

plusone3055

ASKER

no sir I am using the same smaple file
I am launching it in via .BAT File

cscript Script.vbs sample.csv 1.txt

plusone3055

ASKER

Here is what I am using and a screenshot of the error

sample.csv
bat.txt
script.vbs

ASKER CERTIFIED SOLUTION

sirbounty

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

plusone3055

ASKER

the ACT_ID is correct but the INTRO_DATE is now incorrect
im getting back

1.6.40      6/20/2011      6/20/2011
1.3.40      6/20/2011      6/20/2011
1.4.40      6/20/2011      6/20/2011
1.10.40      6/20/2011      6/20/2011
1.60.401      6/20/2011      6/20/2011

Needs to be
1.6.40      06/20/2011      06/20/2011
1.3.40      06/20/2011      06/20/2011
1.4.40      06/20/2011      06/20/2011
1.10.40      06/20/2011      06/20/2011
1.60.401      06/20/2011      06/20/2011

date needs to be mm/dd/yyyy
becuase the application its importing into only takes mm/dd/yyyy format it

plusone3055

ASKER

I got it
made both fields
adVarChar, MaxCharacters

Thanks so much for your help :)

sirbounty

Glad you got it working - thanks for the grade! :^)