?
Solved

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

Posted on 2002-04-18
12
Medium Priority
?
306 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
Industry Leaders: 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

Independent Software Vendors: 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

Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month8 days, 2 hours left to enroll

765 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