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 null in Sql Server database

Hi,

I'm using this source code in order to set the DateTime field of my Sql Server database to null.
I am retreiving dates from an excel sheet. If no date is found, then I set my variable myDate to DateTime.MinValue then i test it just before feeding my database.

I have an error saying that 'object' does not contain definition for 'Value'.

In french :Message d'erreur du compilateur:CS0117: 'object' ne contient pas de définition pour 'Value'
dbCommand.Parameters["@.DateRDV"].Value = System.Data.SqlTypes.SqlDateTime.Null;

The funny thing is that in the class browser i can see the Value property for the class Object...

C#, asp.net
string sqlStmt ;
string conString ;
SqlConnection cn =null;
SqlCommand cmd =null;
SqlDateTime sqldatenull ;
try
{
sqlStmt = "insert into Emp (Date) Values (@.Date) ";
conString = "server=localhost;database=Northwind;uid=sa;pwd=;";
cn = new SqlConnection(conString);
cmd = new SqlCommand(sqlStmt, cn);
cmd.Parameters.Add(new SqlParameter("@.Date", SqlDbType.DateTime));
sqldatenull = System.Data.SqlTypes.SqlDateTime.Null;
if (myDate == DateTime.MinValue)
{
cmd.Parameters ["@.Date"].Value =sqldatenull ;
}
else
{
cmd.Parameters["@.Date"].Value = myDate;
}
cn.Open();
cmd.ExecuteNonQuery();
Label1.Text = "Record Inserted Succesfully";
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
finally
{
cn.Close();
}

Are you sure you're referencing the correct Parameter? The error message says "@.DataRDV" but your code uses "@.Date".

|||

GranPas wrote:

Hi,

I'm using this source code in order to set the DateTime field of my Sql Server database to null.
I am retreiving dates from an excel sheet. If no date is found, then I set my variable myDate to DateTime.MinValue then i test it just before feeding my database.

I have an error saying that 'object' does not contain definition for 'Value'.

In french :Message d'erreur du compilateur:CS0117: 'object' ne contient pas de définition pour 'Value'
dbCommand.Parameters["@.DateRDV"].Value = System.Data.SqlTypes.SqlDateTime.Null;

The funny thing is that in the class browser i can see the Value property for the class Object...

C#, asp.net
string sqlStmt ;
string conString ;
SqlConnection cn =null;
SqlCommand cmd =null;
SqlDateTime sqldatenull ;
try
{
sqlStmt = "insert into Emp (Date) Values (@.Date) ";
conString = "server=localhost;database=Northwind;uid=sa;pwd=;";
cn = new SqlConnection(conString);
cmd = new SqlCommand(sqlStmt, cn);
cmd.Parameters.Add(new SqlParameter("@.Date", SqlDbType.DateTime));
sqldatenull = System.Data.SqlTypes.SqlDateTime.Null;
if (myDate == DateTime.MinValue)
{
cmd.Parameters ["@.Date"].Value =sqldatenull ;
}
else
{
cmd.Parameters["@.Date"].Value = myDate;
}
cn.Open();
cmd.ExecuteNonQuery();
Label1.Text = "Record Inserted Succesfully";
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
finally
{
cn.Close();
}

Did you add a parameter called "@.DateRDV"?

|||

Thanks for help. I actually changed my variable name which was DateRDV to Date because the source code i had pasted was from a sample i found on Internet.

I found a solution to my problem. When the user click on a button, I set myDate to DateTime.MinValue if myDate is null, as i did before. Now, I am using a function in order to insert the date in my database. This is working and I still don't know why the older source code did not. Here is my source working :

int Insert_Trdv(System.DateTime dateRDV)
{
string connectionString = "server=\'myServer\'; user id=\'myId\';
password=\'myPassword\'; database=\'myPassword\'";
System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
string queryString = @."INSERT INTO [Trdv] ([DateRDV])";
System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;
System.Data.IDataParameter dbParam_dateRDV = new
System.Data.SqlClient.SqlParameter();
dbParam_dateRDV.ParameterName = "@.DateRDV";
if(dateRDV == DateTime.MinValue)
{
dbParam_dateRDV.Value = DBNull.Value;
}
else
{
dbParam_dateRDV.Value = dateRDV;
}
dbParam_dateRDV.DbType = System.Data.DbType.DateTime;
dbCommand.Parameters.Add(dbParam_dateRDV);
int rowsAffected = 0;
dbConnection.Open();
try
{
rowsAffected = dbCommand.ExecuteNonQuery();
}
finally
{
dbConnection.Close();
}
return rowsAffected;
}

Thx

Datetime Filter

For one of the tables on my report i need to allow only the records where a
certain datetime field has Null value. I have not been able to figure out the
expression in the filter that would do this.
Any ideas?
I cannot do this in the select statement as other data regions require
records with values in the same field.You can do a filter at the table level (the Table report object, that is) or
at the list level by high-lighting the Table or List (for instance) and
selecting the filters from the Properties menu and placing a filter on the
data in the object. Hope this helps.
"Nice_Out" wrote:
> For one of the tables on my report i need to allow only the records where a
> certain datetime field has Null value. I have not been able to figure out the
> expression in the filter that would do this.
> Any ideas?
> I cannot do this in the select statement as other data regions require
> records with values in the same field.|||Thanks. I have figured out that I can do a filter.
What I am having trouble with is the expression to use in the filter. I get
the feeling that it does not like to handle Nulls.
"Rand" <Rand@.discussions.microsoft.com> wrote in message
news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> You can do a filter at the table level (the Table report object, that is)
or
> at the list level by high-lighting the Table or List (for instance) and
> selecting the filters from the Properties menu and placing a filter on the
> data in the object. Hope this helps.
> "Nice_Out" wrote:
> > For one of the tables on my report i need to allow only the records
where a
> > certain datetime field has Null value. I have not been able to figure
out the
> > expression in the filter that would do this.
> >
> > Any ideas?
> >
> > I cannot do this in the select statement as other data regions require
> > records with values in the same field.|||Sorry about that. If I had read more closely I would have seen that. Have
you tried the following in your filter?
set Expression to your date/time field
set Operator to "="
set Value to "= Nothing"
This is assuming you do not also need to include a date spread for when the
field does have a value. If so, let me know. I have that figured out as
well.
"Nice_Out" wrote:
> Thanks. I have figured out that I can do a filter.
> What I am having trouble with is the expression to use in the filter. I get
> the feeling that it does not like to handle Nulls.
> "Rand" <Rand@.discussions.microsoft.com> wrote in message
> news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> > You can do a filter at the table level (the Table report object, that is)
> or
> > at the list level by high-lighting the Table or List (for instance) and
> > selecting the filters from the Properties menu and placing a filter on the
> > data in the object. Hope this helps.
> >
> > "Nice_Out" wrote:
> >
> > > For one of the tables on my report i need to allow only the records
> where a
> > > certain datetime field has Null value. I have not been able to figure
> out the
> > > expression in the filter that would do this.
> > >
> > > Any ideas?
> > >
> > > I cannot do this in the select statement as other data regions require
> > > records with values in the same field.
>
>|||Hey thanks!
That worked groovy.
I swear I tried everything so close to that. Don't know how I missed it.
"Rand" <Rand@.discussions.microsoft.com> wrote in message
news:DCE9E96F-CD79-4D43-B03A-88204095C6C0@.microsoft.com...
> Sorry about that. If I had read more closely I would have seen that.
Have
> you tried the following in your filter?
> set Expression to your date/time field
> set Operator to "="
> set Value to "= Nothing"
> This is assuming you do not also need to include a date spread for when
the
> field does have a value. If so, let me know. I have that figured out as
> well.
>
> "Nice_Out" wrote:
> > Thanks. I have figured out that I can do a filter.
> > What I am having trouble with is the expression to use in the filter. I
get
> > the feeling that it does not like to handle Nulls.
> >
> > "Rand" <Rand@.discussions.microsoft.com> wrote in message
> > news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> > > You can do a filter at the table level (the Table report object, that
is)
> > or
> > > at the list level by high-lighting the Table or List (for instance)
and
> > > selecting the filters from the Properties menu and placing a filter on
the
> > > data in the object. Hope this helps.
> > >
> > > "Nice_Out" wrote:
> > >
> > > > For one of the tables on my report i need to allow only the records
> > where a
> > > > certain datetime field has Null value. I have not been able to
figure
> > out the
> > > > expression in the filter that would do this.
> > > >
> > > > Any ideas?
> > > >
> > > > I cannot do this in the select statement as other data regions
require
> > > > records with values in the same field.
> >
> >
> >

DATETIME as Primary Key

Hello All,
We have a strange issue.
Environment:
SQL 2K SP3a
TableA Definitioin:
Col1 varchar(10) NOT NULL
Col2 datetime NOT NULL default getdate()
Primary Key col1 and col2
Many inserts are occuring and we are receiving primary key vilolations.
The Insert statement does not explicitly select the datetime just uses the
default on the column declaration.
I.E INSERT TABLE1(col1)
SELECT 'TEST' FROM MyTable
Anyone have an idea why?Fred,
datetime measures time only in increments of 1/300 of a
second. Many rows can be inserted within a single "tick"
of datetime, and apparently this is the case for you, some
of them having matching Col1 values.
It sounds like (Col1, Col2) is not a good choice of primary key.
Steve Kass
Drew University
FredG wrote:

>Hello All,
>We have a strange issue.
>Environment:
>SQL 2K SP3a
>TableA Definitioin:
>Col1 varchar(10) NOT NULL
>Col2 datetime NOT NULL default getdate()
>Primary Key col1 and col2
>Many inserts are occuring and we are receiving primary key vilolations.
>The Insert statement does not explicitly select the datetime just uses the
>default on the column declaration.
>I.E INSERT TABLE1(col1)
> SELECT 'TEST' FROM MyTable
>Anyone have an idea why?
>
>|||Hi Steve,
That makes great sense. So, if the inserts are happening very frequently,
within that 1/300 of a second window, this would cause the error.
"Steve Kass" wrote:

> Fred,
> datetime measures time only in increments of 1/300 of a
> second. Many rows can be inserted within a single "tick"
> of datetime, and apparently this is the case for you, some
> of them having matching Col1 values.
> It sounds like (Col1, Col2) is not a good choice of primary key.
> Steve Kass
> Drew University
> FredG wrote:
>
>|||That's because rows (with the same Col1 value) are being inserted
within 300 milliseconds of each other
Denis the SQL Menace
http://sqlservercode.blogspot.com/
FredG wrote:
> Hello All,
> We have a strange issue.
> Environment:
> SQL 2K SP3a
> TableA Definitioin:
> Col1 varchar(10) NOT NULL
> Col2 datetime NOT NULL default getdate()
> Primary Key col1 and col2
> Many inserts are occuring and we are receiving primary key vilolations.
> The Insert statement does not explicitly select the datetime just uses the
> default on the column declaration.
> I.E INSERT TABLE1(col1)
> SELECT 'TEST' FROM MyTable
> Anyone have an idea why?|||FredG wrote:
> Hello All,
> We have a strange issue.
> Environment:
> SQL 2K SP3a
> TableA Definitioin:
> Col1 varchar(10) NOT NULL
> Col2 datetime NOT NULL default getdate()
> Primary Key col1 and col2
> Many inserts are occuring and we are receiving primary key vilolations.
> The Insert statement does not explicitly select the datetime just uses the
> default on the column declaration.
> I.E INSERT TABLE1(col1)
> SELECT 'TEST' FROM MyTable
> Anyone have an idea why?
If your inserts occur less than 1/300th of a second apart or if you do
multiple row inserts then you will get duplicates. Also you've used
local time, which means you could get duplicates if your local time is
adjusted for DST and then back again.
If you need to record time more precisely then you'll have to populate
some other time value without relying on GETDATE() and DATETIME.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi David,
You mention that duplicate values would be generated because of DST.
How would you get around this problem? I've never really thought about
it until seeing your post.
I don't have a specific example or problem - just curious really.
Thanks
Barry|||Barry wrote:
> Hi David,
> You mention that duplicate values would be generated because of DST.
> How would you get around this problem? I've never really thought about
> it until seeing your post.
Always make sure that your system is down for maintenance during that
one hour window each year :-)
-Tom.|||Ha! I like your style...just kick the plug out ;-)|||Barry wrote:
> Hi David,
> You mention that duplicate values would be generated because of DST.
> How would you get around this problem? I've never really thought about
> it until seeing your post.
> I don't have a specific example or problem - just curious really.
> Thanks
> Barry
Use GETUTCDATE() instead.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Ah right ok.
Thanks
Barry

Datetime and null value

In my stored procedure i'm extracting datevalues from a table and printing t
hem
in yyyy-mm-dd hh:mi:ss format.
for example if the table value is 'Jul 16 2004 12:00AM' then my statement
(which is dynamically generated)
select Convert(CHAR(20),cast('Jul 16 2004 12:00AM ' as datetime),20)
will print " 2004-07-16 00:00:00 "
but if the table's datevalue is null then the statement
select Convert(CHAR(20),cast(' ' as datetime),20)
is printing "1900-01-01 00:00:00 "
I want the second one to be blank value(' ') what should I do?
Thanks
Chandra
Declare @.t datetime
set @.t='2005-07-16 12:00:00'
select case when @.t is null then convert(varchar,'',101) else
Convert(CHAR(20),cast(@.t as datetime),20) end
set @.t=null
select case when @.t is null then convert(varchar,'',101) else
Convert(CHAR(20),cast(@.t as datetime),20) end
Madhivanan|||Actually I mentioned that it is dynamically generated statement
like the following
select 'insert into employee (hire_date) values ( Convert(CHAR(20),cast('''+
isnull(cast(Hire_date as char),'')+ ''' as datetime),20))' from employee
will give you an insert statement.
this insert statement when run, will insert the data into table.
at this point I'm having the problem as the insert statement is inserting
default date(1900...) for empty strings(actually null values)
thanks
chandra
"Madhivanan" wrote:

>
> Declare @.t datetime
> set @.t='2005-07-16 12:00:00'
> select case when @.t is null then convert(varchar,'',101) else
> Convert(CHAR(20),cast(@.t as datetime),20) end
> set @.t=null
> select case when @.t is null then convert(varchar,'',101) else
> Convert(CHAR(20),cast(@.t as datetime),20) end
>
> Madhivanan
>|||Chandra
declare @.dt datetime
set @.dt =''
select @.dt
--1900-01-01 00:00:00.000
select case when @.dt ='' then null else @.dt end as d
--NULL
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:FEA86C39-4E3D-4D66-9588-1421CB53A918@.microsoft.com...
> Actually I mentioned that it is dynamically generated statement
> like the following
> select 'insert into employee (hire_date) values (
> Convert(CHAR(20),cast('''+
> isnull(cast(Hire_date as char),'')+ ''' as datetime),20))' from employee
> will give you an insert statement.
> this insert statement when run, will insert the data into table.
> at this point I'm having the problem as the insert statement is inserting
> default date(1900...) for empty strings(actually null values)
> thanks
> chandra
>
> "Madhivanan" wrote:
>|||> Actually I mentioned that it is dynamically generated statement
> like the following
> select 'insert into employee (hire_date) values (
> Convert(CHAR(20),cast('''+
> isnull(cast(Hire_date as char),'')+ ''' as datetime),20))' from employee
> will give you an insert statement.
> this insert statement when run, will insert the data into table.
> at this point I'm having the problem as the insert statement is inserting
> default date(1900...) for empty strings(actually null values)
Can you tell us what you WANT to insert when the Hire_date is NULL?
I'll make a guess:
SELECT 'INSERT employee (hire_date)
SELECT '+COALESCE(CONVERT(VARCHAR(8), Hire_date, 112), 'NULL')
FROM employee|||> SELECT 'INSERT employee (hire_date)
> SELECT '+COALESCE(CONVERT(VARCHAR(8), Hire_date, 112), 'NULL')
> FROM employee
Whoops, should be:
SELECT 'INSERT employee (hire_date)
SELECT '+COALESCE(''''+CONVERT(VARCHAR(8), Hire_date, 112)+'''', 'NULL')
FROM employee

Datediff formula on insert returning null value

I have a form with two date fields that the user will submit their requested vacation time off with. When they insert it, I am trying to say find the difference between the request_start_date and request_end_date in days MINUS any of the days they would already have off like weekends or holidays that are included in another table. Everything inserts okay, but I am getting null for the request_duration. If I put dates in quotes and run the query it comes back with the right results. If I put the dates in the form and submit it, I get Null for the request_duration.

Thank you in advnace for any help on this!

INSERTrequest(emp_id,request_submit_date, request_start_date,request_end_date,request_duration,request_notes,time_off_id)Select@.emp_id,GETDATE(),@.request_start_date,@.request_end_date, 1 +DATEDIFF(day, @.request_start_date, @.request_end_date) - (selectcount(*)from WeekEndsAndHolidayswhere DayOfWeekDatebetween @.request_start_dateand @.request_end_date),@.request_notes,@.time_off_id

Either request_start_date or request_end_date is coming across as null. Are you sure that request_duration is the only null field?|||

Motley,

Thanks for the response. It took a while to get posted and I figured it out way before it got posted. I had worked on it for about an hour before I posted this and figure it out 5 minutes after I posted it. I didn't have one of my text boxes bound correctly.

datediff

when I create the table I used

[from_time] [smalldatetime] NULL ,
[to_time] [smalldatetime] NULL ,
[total_hrs] AS (datediff(hh,[from_time],[to_time])) ,

but the hours calculation is comming wrong. moreover i want set the format like hh:mi. but i could not. pls helpRefer to DATEPART function under books online.#

HTH|||elams if your goal is to return elapsed time, you might want to change your calculated field to hold minutes or seconds rather than hours, this would make it easier to re-format.

as an alternative, here is a function that will return elapsed time.

Create function fn_ElapsedTime (
@.starttime datetime,
@.endtime datetime = Null)
returns varchar(40)
as
begin
declare @.d int, @.h int, @.m int, @.s int, @.ms int, @.dif1 int, @.ret varchar(40)
select @.d = 0, @.h = 0, @.m = 0, @.s = 0, @.ms = 0

set @.d = datediff(dd,@.starttime,@.endtime)
set @.dif1 = datediff(ms,dateadd(dd,@.d,@.starttime),@.endtime)

if (@.dif1 > 0) begin
set @.ms = @.dif1 % 1000
set @.dif1 = @.dif1 - @.ms
set @.s = ((@.dif1 / 1000) % 60)
set @.dif1 = @.dif1 - (@.s * 1000)
set @.m = ((@.dif1 / 60000) % 60)
set @.dif1 = @.dif1 - (@.m * 60000)
set @.h = ((@.dif1 / 3600000) % 60)
end

set @.ret = cast(@.d as varchar(25)) + ':' +
right('00' + cast(@.h as varchar(2)),2) + ':' +
right('00' + cast(@.m as varchar(2)),2) + ':' +
right('00' + cast(@.s as varchar(2)),2) + ':' +
right('000' + cast(@.ms as varchar(3)),3)

return @.ret
end

GO

create table #Tmp(
from_time smalldatetime
, to_time smalldatetime
, total_hrs as (datediff(hh,from_time, to_time))
)
insert into #Tmp values(getdate(), dateadd(dd,4,getdate()))
insert into #Tmp values(getdate(), dateadd(hh,2,getdate()))
insert into #Tmp values(getdate(), dateadd(mi,3,getdate()))
select from_time
, to_time
, total_hrs
, dbo.fn_ElapsedTime(from_time,to_time) as Elapsed_Time
From #Tmp|||it works. Thanks mate
elam