Date In String question

[xlbo]

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):

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

 

[xlbo]

no worries - got the answer - just need to add:

stDate = "{ ts '" & format(Now,"yyyy-mm-dd hh:mm:ss") & "'}"


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