Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2002-04-18
12
Medium Priority
?
308 Views
Last Modified: 2010-04-04
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  
0
Comment
Question by:windyui
  • 6
  • 2
  • 2
  • +1
12 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6952549
use parameters in your query,
do you use a ado or bde connection?
0
 

Author Comment

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

windyui
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6952662
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 ;-)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:kretzschmar
ID: 6952667
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 ;-)
0
 
LVL 1

Expert Comment

by:JDN
ID: 6952701
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
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6952721
thats already stated above, jdn,
well, but not so explicit :_)
0
 
LVL 17

Expert Comment

by:geobul
ID: 6952759
DateTime is a float value, so you may use 0 (zero) instead of NULL.

Regards, Geo
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6952776
nope, that didn't work, geo (0 <> NULL)
0
 
LVL 1

Accepted Solution

by:
JDN earned 400 total points
ID: 6952846
The only way to do this is, as I already stated:


Update CRAZY set ApplyDate=Null where ...

and

Select ApplyDate from CRAZY where ApplyDate is Null


Note the "=" in de UPDATE and the "is" in the SELECT; this is essential.

JDN
0
 

Author Comment

by:windyui
ID: 6952980
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
   
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6952993
hmmm . . .
0
 
LVL 17

Expert Comment

by:geobul
ID: 6953128
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question