Solved

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

Posted on 2002-04-18
12
300 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
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 hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

867 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

15 Experts available now in Live!

Get 1:1 Help Now