Hi - having some issues with uploading a date into an SQL Server table. Data is in MS Excel and I am using VBA to try to insert recrods into a control table. The following works (kind of):
This however, ends up with "01/01/1900" in the db table. I can get a value to be entered if I use
stDate = 39080
ie using the serial number for the date but there seems to be an issue in that when I check the date in the table, it is showing as 31/12/2006 rather than the 29/12/2006
Can anyone shed any light on this and/or show me the syntax for passing the date through as a date rather than a date serial....
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question
Code:
Dim strCon As ADODB.Connection
Set strCon = New ADODB.Connection
stDate = Now
strCon.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=KPI;Data Source=LOTHIAN"
strCon.Execute ("INSERT INTO KPIHotTopicControl ( HTTitle, HTReportTypeKey, HTPriority, HTDateLoaded) SELECT 'TitleTest','HTByArea','1'," & stDate & ",")
This however, ends up with "01/01/1900" in the db table. I can get a value to be entered if I use
stDate = 39080
ie using the serial number for the date but there seems to be an issue in that when I check the date in the table, it is showing as 31/12/2006 rather than the 29/12/2006
Can anyone shed any light on this and/or show me the syntax for passing the date through as a date rather than a date serial....
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question