SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 DTS Date Insert Script
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Dazza1883
Starting Member

5 Posts

Posted - 06/12/2009 :  04:31:57  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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) & ")"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.42 seconds. Powered By: Snitz Forums 2000