Date type compatibility between VB and SQL

[danieln4]

I am calling a SQL stored procedure from VB for a recordset of date field in SQL. Does anyone know if the SQL datetime type and VB date type are compatible? Do I need to do some sort of conversion before the SQL datetime type value is assigned to a VB date value?

 

[chadt]

CDate('YOUR FIELD)

When I pass a parameter to SQL via a stored procedure it has to be like....

'///////////////
Set pLastUpd = .CreateParameter("@LASTUPD_18", adDate, adParamInput, 8, CDate(mLastUpd))
'///////////////

 

[SemperFiDownUnda]

VB's DATE is a DOUBLE that 0 = 30 Dec 1899 00:00:00
SQL Servers DATE is a floating point number where 0 = 1 Jan 1900 00:00:00

That being said when you go through ADO you don't have to worry about that.

I always use textual date representation for sending dates to SQL.

but here CREATE this table in Northwind

CREATE TABLE TEST
( MyDate DATETIME )

then put this code in a button on a form

    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=YourServerName"
    conn.Open
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Set rs.ActiveConnection = conn
    rs.Open "Test", , adOpenDynamic, adLockOptimistic
    Dim dt As Date
    dt = 0
    MsgBox Format(dt, "DD MMM YYYY HH:MM:SS")
    rs.AddNew
    rs.Fields("MyDate").Value = dt
    rs.Update
    rs.Close
    conn.Close
    set rs = nothing
    set conn = nothing

then run it then go back to Query analyser and run this
SELECT CONVERT(DECIMAL(18,8), MyDate) FROM TEST

You'll see that the processes of going thru ADO takes care of changing the 0 to -2