asked on

MS SQL SERVER ...DateTime field make me crazy~~~! HELP!!

hihi...
I create a field called "ApplyDate" in a table named "CRAZY" of the MS SQL SERVER 7.0. Field Type is "DateTime". This is the Nightmare....
When I set the Field to Empty :

Update CRAZY set ApplyDate='' where ....

Then,I select the ApplyDate Use this condition..

Select ApplyDate from CRAZY where ApplyDate='';

Nothing Happens...Because the Field "ApplyDate" have been auto set to '1900/01/01'.....How can I select using the condition "ApplyDate=''"???HOW??

and Then,I try to set the Field to "NULL";

Update CRAZY set ApplyDate=Null where...;

Then I select using this condition:

Select ApplyDate from CRAZY where ApplyDate=Null;

GOD`~~~!! Nothing have been selected again!!! Althrough I find the field is empty in the SQL Explorer.....
HOW..........can I do ....Using Delphi 4.0.....
HOW...Can I select using "ApplyDate=''" condition...HELP...HELP..... T_T

windyui

kretzschmar

use parameters in your query,
do you use a ado or bde connection?

windyui

ASKER

hihi..

YEs.I Use BDE through ODBC...
How to use parameters in query??
ParamByName('ApplyDate').asString...????
It doesn't work too.. >_<

windyui

kretzschmar

well do something like this

const
sql_text = 'select * from atable where adate = :pdate';
begin
query1.colse;
query1.sql.text := sqltext;
query1.parambyname('pdate').asdatetime := your_tdatetime_var;
query1.open;
end;

advantages by using paramaters,
- a static sql-statement, no need to build it on the fly
- dateformat-independent

meikl ;-)

kretzschmar

appendix

you can use parameters also in update insert delete-statements, but uase the query.execsql instead of query.open.

about NULL fields selects
the where clause must be then
where adate is null
(parameters cannot used used for null-selects)

but for update with null use simple
query1.parambyname('pdate').clear;
(parameters can used for null-inserts and updates)

meikl ;-)

JDN

Winduyl,

This is your problem here:

Select ApplyDate from CRAZY where ApplyDate=Null

You should use instead:

Select ApplyDate from CRAZY where ApplyDate is Null

The "is" must be used instead of the "=" in conjunction with the Null value.

JDN

kretzschmar

thats already stated above, jdn,
well, but not so explicit :_)

geobul

DateTime is a float value, so you may use 0 (zero) instead of NULL.

Regards, Geo

kretzschmar

nope, that didn't work, geo (0 <> NULL)

ASKER CERTIFIED SOLUTION

JDN

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

windyui

ASKER

hihi,JDN
my name is windyui instead of Winduyl ^_^.
Thank you for you answer. I give you the points because it is the most simple solution and it works fine.
Thank you Meikl too ^O^ !!

thank you every one.

windyui

kretzschmar

hmmm . . .

geobul

It depends on the logic of the usage of that field - empty vs. null. Null is 'value not set'. Empty is 'Value is set but empty'. This can easily be shown using string fields. A stringfield has null value when a new record is added but the field value is not set. Later that field can be assigned an empty value '', which is different from null.

I meant:

Update CRAZY set ApplyDate=0 where ....

Select ApplyDate from CRAZY where ApplyDate=0;

This is for avoiding nulls in the field. We presume that Date=0 doesn't exist in reality and therefore the value 0 means empty (but set, i.e. not null).

But that's not what has been asked :)

Regards, Geo