| Author |
 Topic  |
|
|
Dazza1883
Starting Member
5 Posts |
 Posted - 06/12/2009 : 04:31:57
|
I am trying to insert some dates from a csv text file along with the date of the monday from the week before. I have wrote a script which seems to 99% work but the date value on insertion always defaults to 01/01/1900 and not the date cvalue that should be inserted any help would be greatly appreciated, thanks
---------------------------
Dim objADORS
Dim objADOCnn
Set objADOCnn = CreateObject("ADODB.Connection")
objADOCnn.Open "CONNECTION DETAILS"
Dim CurrentDate
Dim CurrentDay
Dim DateVal
CurrentDate = Date
CurrentDay = WeekDay(CurrentDate)
If CurrentDay = 2 Then DateVal = Date -7
If CurrentDay = 3 Then DateVal = Date -8
If CurrentDay = 4 Then DateVal = Date -9
If CurrentDay = 5 Then DateVal = Date -10
If CurrentDay = 6 Then DateVal = Date -11
If CurrentDay = 7 Then DateVal = Date -12
If CurrentDay = 1 Then DateVal = Date -13
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("\\fshq3294w\c$\Documents and Settings\clucasi\Desktop\timenet.txt", ForReading)
Const ForReading = 1
Dim arrFileLines()
i = 0
Do Until objFile.AtEndOfStream
'Redim Preserve arrFileLines(i)
strLine = objFile.ReadLine
arrTimenetValues = split(strLine, ",")
Insert_SQL = "INSERT INTO app_per40_data (empno,period_date,department,week_hours) VALUES (" & replace(arrTimenetValues(0),"""","") & ", " & DateVal & ", '" & replace(arrTimenetValues(4),"""","") & "', " & arrTimenetValues(5) & ")"
'MsgBox Insert_SQL
objADOCnn.Execute Insert_SQL, , adExecuteNoRecords
i = i + 1
Loop
objFile.Close
msgbox DateVal
Main = DTSTaskExecResult_Success
End Function |
|
|
Dazza1883
Starting Member
5 Posts |
Posted - 06/12/2009 : 05:40:16
|
Problem solved with a single quote around the double quotes
Insert_SQL = "INSERT INTO app_per40_data (empno,period_date,department,week_hours) VALUES (" & replace(arrTimenetValues(0),"""","") & ", '" & DateVal & "', '" & replace(arrTimenetValues(4),"""","") & "', " & arrTimenetValues(5) & ")"
|
 |
|
| |
Topic |
|
|
|
DTS Date Insert Script
