Solved

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

Posted on 2002-04-18
12
297 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now