VB6 and ms SQL 2005 DATETIME

7 Aug 2006 1:39 PM

dan688

Morning,
Could'nt find a post / site that has addressed this issue and I
would be very surprised if I'm the first to find this.

I am currently having an issue passing a parameter via a stored procedure in
vb6 to SQL 2005 that is requiring a datetime format.

VB6 supports these separatly i.e. DATE, TIME were as VB.Net etc. support
this as DATETIME.

Is there any way I can get the date into the SQL 2005 database, not fused
about the time part as this would help being stored as 00:00:00.

What I've tried so far is:

strDate = Format(strDate, "yyyy-mm-dd 00:00:00")

but when passing through it details parameter not supported:

..Parameters.Append .CreateParameter("dteDate", adDate, adParamInput, ,
dteDate)

Any idea's?

Many thanks,

Dan

7 Aug 2006 2:09 PM

Ken Halter

In Loving Memory - http://www.vbsight.com/Remembrance.htm
Show quoteHide quote

"dan688" <dan***@discussions.microsoft.com> wrote in message
news:68EE4C4F-73A0-4D4E-BCA0-17E44B162214@microsoft.com...
> Morning,
> Could'nt find a post / site that has addressed this issue and I
> would be very surprised if I'm the first to find this.
>
> I am currently having an issue passing a parameter via a stored procedure
> in
> vb6 to SQL 2005 that is requiring a datetime format.
>
> VB6 supports these separatly i.e. DATE, TIME were as VB.Net etc. support
> this as DATETIME.
>
> Is there any way I can get the date into the SQL 2005 database, not fused
> about the time part as this would help being stored as 00:00:00.
>
> What I've tried so far is:
>
> strDate = Format(strDate, "yyyy-mm-dd 00:00:00")
>
> but when passing through it details parameter not supported:
>
> .Parameters.Append .CreateParameter("dteDate", adDate, adParamInput, ,
> dteDate)
>
> Any idea's?
>
> Many thanks,
>
> Dan

When you use the Format$ function, you're converting the value to a string,
no matter what.

VB6 has separate Date/Time functions but that has nothing to do with the
Date data type. They're simply functions that extract either the Date or the
Time from a Date datatype. There's no way to store only a time or date in
the Date datatype.

Dim WhatEver As Date
WhatEver = Now ' This is the current date/time and is a true Date datatype.
that any database should accept.

WhatEver = #2006/08/07# is also a true Date data type.
WhatEver = "2006/08/07" is not. It's a string.

--
Ken Halter - MS-MVP-VB (visiting from VB6 world) - http://www.vbsight.com
Please keep all discussions in the groups..

7 Aug 2006 2:44 PM

dan688

Do you have any idea what I would pass the parameter type through as because
adDate is not valid!

7 Aug 2006 3:07 PM

Ken Halter

"dan688" <dan***@discussions.microsoft.com> wrote in message
news:47797529-AF55-4092-A474-8384D2E5C710@microsoft.com...
> Do you have any idea what I would pass the parameter type through as
> because
> adDate is not valid!

'======
Private Sub Command1_Click()
   Dim dt As Date

   dt = Now
   dt = CDate(Format$(dt, "yyyy-mm-dd"))

   Debug.Print dt '<-- pass dt. this should contain only the date

End Sub
'======

--
Ken Halter - MS-MVP-VB (visiting from VB6 world) - http://www.vbsight.com
Please keep all discussions in the groups..
In Loving Memory - http://www.vbsight.com/Remembrance.htm

7 Aug 2006 3:20 PM

Cor Ligthert [MVP]

Dan,

I don't know if you are an (USA) American, than you can use DateTime
literals, try to avoid outside that world those and just use the New
datetime which is conform ISO.

Unlucky enough does the VB debugger (not the C# debugger) only represents
datetimes in USA American notation, while the internal format has nothing to
do with that.

Beside that is the *DateTime* in the Microsoft Access and Jet Servers not a
string but a value representing ticks in 1000/3 seconds. Therefore you never
can say that a date or a time is alone stored.

I hope this gives an idea

Cor

Show quoteHide quote

"dan688" <dan***@discussions.microsoft.com> schreef in bericht
news:47797529-AF55-4092-A474-8384D2E5C710@microsoft.com...
> Do you have any idea what I would pass the parameter type through as
> because
> adDate is not valid!