CODEWITHSUNDEEP
sqlvba
Mark
Mark

Reputation: 11

Why does 1900-01-01 00:00:00.000 appear after inserting data from excel into SQL table?

I am trying to insert dates from a worksheet into a table into SQL. The original source for the date in the worksheet is from a connection to sql and the date field has been set up with a Cast(interview_date as date) but once the worksheet has been populated I need to insert back in to another table in SQL. When I do, i get 1900-01-01 00:00:00.000 in the date field(which is is date time) of the table. My extract into excel date format is yyyy-mm-dd and its the same on the Sql table i am inserting into.

I have tried the following insert in VBA, the 3 fields in question are sdate_confirmed, sdate_interview and rundate.

I have declared the fields as 

Dim sForename As String
Dim sSurname As String
Dim sdate_confirmed As Date
Dim sdate_interview As Date
Dim sNumerator1 As Integer
Dim sDenominator1 As Integer
Dim sNumerator2 As Integer
Dim sDenominator2 As Integer
Dim sNumerator3 As Integer
Dim sDenominator3 As Integer
Dim sRundate As Date
Dim sDO_NOT_DELETE As Integer

I am using Excel 2010 and SQL server 2012.

Thanks for any help in advance.

I have tried cdate() and datevalue() in the VBA insert into...

there are no null or missing dates in the excel worksheet.

oCon.Execute "Insert into [Interview_Info].[dbo].Interview (Forename,Surname,date_confirmed,date_interview,Numerator1,Denominator1,Numerator2,Denominator2,Numerator3,Denominator3,Rundate,DO_NOT_DELETE) " & _
             "Values ('" & sForename & "','" & sSurname & "'," & sdate_confirmed & "," & sdate_interview & ",'" & sNumerator1 & "','" & sDenominator1 & "','" & sNumerator2 & "','" & sDenominator2 & "','" & sNumerator3 & "','" & sDenominator3 & "'," & sRundate & ",'" & sDO_NOT_DELETE & "')"

I am hoping that dates in the fields in Excel will be inserted into the [Interview_Info].[dbo].Interview will have the correct dates as shown in the excel worksheet instead of 1900-01-01 00:00:00.000.

Upvotes: 0

Views: 434

Answers (2)

Mark
Mark

Reputation: 11

I resolved it.

I must have needed the nap!

i kept the original script and fixed the format.

it all works.

Upvotes: 1

jefftrotman
jefftrotman

Reputation: 1114

Based on what I'm seeing, I'm surprised the INSERT statement is executing successfully and leaving the date fields blank, but what jumps out at me is - you need to wrap your date values in single quotes the way you are wrapping your strings. Even though you DIM'd them as Date types, when you concatenate them with the other strings to make up the insert statement, they are being cast into strings.

The beginning of your Values clause should look like this:

"Values ('" & sForename & "','" & sSurname & "','" & sdate_confirmed & "',"' & sdate_interview & "','" & ...

I would recommend that - instead of concatenating the command string on the same line as the call to execute it

oCon.Execute "Insert into [Interview_Info]

concatenate your command string into a variable that you can examine (to ensure date values are wrapped in single quotes) before executing it.

string cmd = "Insert into [Interview_Info]...
oCon.Execute cmd

Upvotes: 0

Related Questions