DTS Date Insert Script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS Date Insert Script

Author

Topic

Dazza1883
Starting Member

5 Posts

Posted - 2009-06-12 : 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 - 2009-06-12 : 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) & ")"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources