Datetime returns NULL from stored proc in VB

I execute a stored procedure in VB and get several results... the last one (a date time field) is always returned as NULL even though I can see in enterprise manager that it IS infact set... any ideas?How are you retrieving the results? In a recordset or as a command ouput parameter?|||I'm using ADODB.Recordset|||Huh. I think we're gonna need to see some code on this one.|||I am using a record set. Hopefully you are using ADO 2.7 or higher by now...|||I meant, it would be helpful to see the code you are using, to help troubleshoot the problem.

But, try inserting this after you retrieve your recordset:

Dim mField As Field
For Each mField In mRecordset.Fields
Debug.Print mField.Name & ": " & mField.Type
Next mField

(replacing mRecordset with your recordset name)

Just to see what datatype is being returned. This may help point to the problem.|||Thanks, I really appreciate it... I'll try it right now :)|||The value of the field type is 135 which is

Const adDBTimeStamp = 135 (&H87)|||Any one else got an idea?|||Is TimeStamp the field type in SQL, or is it supposed to be DateTime? They are not the same.|||It's a DateTime data type (just checked in EM)... I know they're not the same... I don't know why it's returning time stamp and why it's coming up NULL (I'm retrieving it into a string, then I tried a Date type)|||As a test, in your sp, try casting the column to be DATETIME in the output query. See if this changes the datatype in VB.|||I fixed it :-D

datetime

create proc dbo.GetList
(
@.OrgList varchar(1000),
@.startDateTime datetime

)
as
begin

declare @.SQL varchar(1000)

set @.SQL = 'Select A.TransactionID,
A.PermitId,
A.IssuingOrganizationId,
A.VehicleId

From Vehicle A WITH (NOLOCK)
join PurchasingCompany B WITH (NOLOCK)
on A.PurchasingCompanyId = B.PurchasingCompanyId
Where
A.IssueDate >= '+ '@.startDateTime' +' And
A.IssuingOrganizationId IN ('+ @.OrgList+')'
exec(@.sql)
end
go

This doesn't work, But If I substitute@.startDateTime with '2/1/2004', it works. I think iam missing some formatting, I tried several ways to make it work. Could anyone tell me how I should do this.

You are including the literal "@.StartDateTime", which is clearly not what you want.

create proc dbo.GetList
(
@.OrgList varchar(1000),
@.startDateTime datetime

)
as
begin

Look at the BOL article on CONVERT to determine what format is right for you - I am using the ODBC canonical format.

declare @.SQL varchar(1000)

set @.SQL = 'Select A.TransactionID,
A.PermitId,
A.IssuingOrganizationId,
A.VehicleId

From Vehicle A WITH (NOLOCK)
join PurchasingCompany B WITH (NOLOCK)
on A.PurchasingCompanyId = B.PurchasingCompanyId
Where
A.IssueDate >= '''+ CONVERT(nvarchar(30),@.startDateTime,120) +''' And
A.IssuingOrganizationId IN ('+ @.OrgList+')'
exec(@.sql)
end
go

|||That really worked for me, thank you so much for the reply.

Dates

trying to create a stored proc with this query:
select * from table1 where datefield BETWEEN @.startdate
AND @.enddate.
When using Crystal reports against this,it works fine
except when you are just using one day- for example your
start date and end date would both be 9/2/2004. Anyone run
into this? Should I do something different? THANKS!probably you are not considering time component. See following example.
create table t(dt datetime)
insert into t values ('20040903 12:24:15')
insert into t values ('20040903 1:24:15')
insert into t values ('20040903 16:24:15')
insert into t values ('20040903')
insert into t values ('20040904')
select * from t
where dt between '20040903' and '20040903 23:59:59'
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
"Ann" <anonymous@.discussions.microsoft.com> wrote in message
news:51bb01c49134$9db6b420$a501280a@.phx.gbl...
> trying to create a stored proc with this query:
> select * from table1 where datefield BETWEEN @.startdate
> AND @.enddate.
> When using Crystal reports against this,it works fine
> except when you are just using one day- for example your
> start date and end date would both be 9/2/2004. Anyone run
> into this? Should I do something different? THANKS!