Solved

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

Posted on 2002-04-18
12
305 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 100 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

728 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