NULL value in datetime type field

[Former Member]

Good Day

Experts:

I am having a bit of trouble with a date field. I get all the data in a recordset object and wish to condition on the

date field. Specifically, I need to find out if the date is NULL or blank then set another field = 1. If it has a valid date in the field then set this other field to a zero. Trouble is when I check to see what is in that date field, it returns 12/30/1899. But if I step through the code and copy the query that is getting executed to QueryAnalyzer and run it, the field shows NULL for a vaule.

Has anyone ever run into this before? Any suggestions how I can handle it?

Thanks for the help,

Ed

[Former Member · Accepted solution]

HI Ed,

This is the database settings (see from date/to date settings).

I am suggesting the following:

use the cast function to convert to nvarchar, then use null...

Demo:

create table x (x int ,y datetime null)

insert into x (x) values(1)
insert into x (x,y) values(2,getdate())
select * from x

select isnull(cast(y as nvarchar),'1') from x
drop table x

Best Regards,

J.

[Former Member · Accepted solution]

Thanks for your replies, but I must explain again as I was unclear in my original explanation.

I am using VB.net and working with data in a SQL database. I am querying the SQL database from my AddOn using the SAP recordset object and executing the DoQuery.

It is clear when I look in the database, and run the same query in Query Analyzer, that this datetime field is NULL. If this field is NULL I need to do one "thing" if it is not NULL I need to do "something" else.

So, when I execute the DoQuery and interogate the value for that field, I get 12/30/1899.

Is this normal? Do I have to condition on whether this field is 12/30/1899 to see if it is NULL. I don't understand this. Usually I thought you would use soemthing like...If IsDbNull(Variable) = True Then...

Thanks for the help,

Ed

[former_member201110 · Accepted solution]

Hi Ed,

The data returned by SQL will contain the null value, regardless of which data retrieval method you are using (eg ADO.NET or DI API recordset object). It is the DI API recordset object that converts this value to the date. You get the same issue if a text field is null in the database but the recordset returns an empty string (rather than null).

Personally, I use the isnull statement to convert nulls in a date field to 01/01/1900 (it's not really very different than testing for 12/30/1899 but if I use the isnull command then I can be sure that if a different patch or version of the DI API returns a different value than 12/30/1899, due to a bug or a design change, then my code will still work).

select isnull(MYDATE, '1900-01-01') as MYDATE from MYTABLE

Kind Regards,

Owen

[Former Member · Accepted solution]

Hello Owen...

When I try --> isnull(I.Date, '1900-01-01') as I.Date I receive an error indicating Incorrect syntax near '.'

Do I have to do anything else special to have this work with the Join? I tried it on a simple query on one table and it works fine.

Thanks,

Ed

[former_member201110 · Accepted solution]

Hi Ed,

Try

isnull(I.Date, '1900-01-01') as Date

Kind Regards,

Owen

[Former Member · Accepted solution]

Excellent...missed that. Thanks for the extra set of eyes.

Thanks,

Ed

[Nussi]

Hi Ed,

i know this problem but i didn't get your need...

when you want to set a edittext datetyme field:

the best is make a if statement and look if the recordset field has the value 12/30/1899

if yes set EditText.String = "" and the edittext datetime is empty.

is that what you're looking for ?

good night

lg David