Setting boolean field from date field values...

Setting boolean field from date field values...

Post by Hugh O'Donnel » Wed, 29 Jan 2003 08:31:51



I'm using SQL Server 2000 and trying to set a boolean column, CurrentDay,
in a table, MyStatus, to true for a certain date (i.e. 1/28/2003).

If I run the code below, the boolean values are reversed:
-=-=-=-=
        UPDATE
                MyStatus
        SET
                CurrentDay = DateDiff(day, PriceDate, '1/3/2002')
-=-=-=-=

If I run the code below, I get an error at the == sign.  How should I do
this?
-=-=-=-=
        UPDATE
                MyStatus
        SET
                CurrentDay = (DateDiff(day, PriceDate, '1/3/2002') == 0)
-=-=-=-=

Thanks for any help you can give me...

Hugh
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

In order to reply via email, please remove the no_spam_for_me_ before
my real email address.

 
 
 

Setting boolean field from date field values...

Post by BP Margoli » Wed, 29 Jan 2003 09:00:41


Hugh,

SQL Server does not support a BOOLEAN data type. The closest thing to a
BOOLEAN data is the BIT data type, which can hold a 0 or a 1. Note that SQL
Server does NOT in any way consider a 0 to be FALSE and a 1 to be TRUE. Your
application can make that interpretation, but your application can just
equally well make the assumption that 0 is TRUE and 1 is FALSE, and SQL
Server is not going to care one whit   :-)

Try something like:

UPDATE  MyStatus
    SET CurrentDay = case DateDiff(day, PriceDate, '1/3/2002') when 0 then 1
else 0 end

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> I'm using SQL Server 2000 and trying to set a boolean column, CurrentDay,
> in a table, MyStatus, to true for a certain date (i.e. 1/28/2003).

> If I run the code below, the boolean values are reversed:
> -=-=-=-=
>     UPDATE
>     MyStatus
>     SET
>     CurrentDay = DateDiff(day, PriceDate, '1/3/2002')
> -=-=-=-=

> If I run the code below, I get an error at the == sign.  How should I do
> this?
> -=-=-=-=
>     UPDATE
>     MyStatus
>     SET
>     CurrentDay = (DateDiff(day, PriceDate, '1/3/2002') == 0)
> -=-=-=-=

> Thanks for any help you can give me...

> Hugh
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

> In order to reply via email, please remove the no_spam_for_me_ before
> my real email address.


 
 
 

Setting boolean field from date field values...

Post by Anith Se » Wed, 29 Jan 2003 09:00:15


There is no 'boolean' datatype in SQL Server. You have to use CHAR or INT
or BIT datatypes to accomodate it. Assuming 1 for true & 0 for false, you
can try:

UPDATE MyStatus
   SET CurrentDay = 1
 WHERE PriceDate = '01/03/2002'

--
- Anith
(Please respond only to newsgroups)

 
 
 

Setting boolean field from date field values...

Post by BP Margoli » Wed, 29 Jan 2003 09:07:25


Anith,

Just because I was, in another thread, brought up short on this recently
:-)

The code you posted is making the assumption that PriceDate holds a time of
midnight    :-)

BPM


Quote:> There is no 'boolean' datatype in SQL Server. You have to use CHAR or INT
> or BIT datatypes to accomodate it. Assuming 1 for true & 0 for false, you
> can try:

> UPDATE MyStatus
>    SET CurrentDay = 1
>  WHERE PriceDate = '01/03/2002'

> --
> - Anith
> (Please respond only to newsgroups)

 
 
 

Setting boolean field from date field values...

Post by Bob Barrow » Wed, 29 Jan 2003 09:07:33


Use a WHERE clause:

   UPDATE
    MyStatus
    SET
    CurrentDay = 1
    WHERE PriceDate = '20020103' --always pass dates in ISO format

If you want to set the column to 0 for records thant don't meet the
criteria, then use CASE:
   UPDATE
    MyStatus
    SET
    CurrentDay = CASE WHEN PriceDate = '20020103' then 1
                            else 0 end

Bob Barrows


Quote:> I'm using SQL Server 2000 and trying to set a boolean column, CurrentDay,
> in a table, MyStatus, to true for a certain date (i.e. 1/28/2003).

> If I run the code below, the boolean values are reversed:
> -=-=-=-=
>     UPDATE
>     MyStatus
>     SET
>     CurrentDay = DateDiff(day, PriceDate, '1/3/2002')
> -=-=-=-=

> If I run the code below, I get an error at the == sign.  How should I do
> this?
> -=-=-=-=
>     UPDATE
>     MyStatus
>     SET
>     CurrentDay = (DateDiff(day, PriceDate, '1/3/2002') == 0)
> -=-=-=-=

> Thanks for any help you can give me...

> Hugh
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

> In order to reply via email, please remove the no_spam_for_me_ before
> my real email address.

 
 
 

Setting boolean field from date field values...

Post by Anith Se » Wed, 29 Jan 2003 09:13:07


Yup, it was a guess. But see, the snippet he posted does not suggest
otherwise, though :-)

--
- Anith
(Please respond only to newsgroups)

 
 
 

Setting boolean field from date field values...

Post by Joe Celk » Wed, 29 Jan 2003 09:23:51


1) There is no BOOLEAN datatype in SQL.  It is considered a weak
programming technique to keep Boolean columns, even using INTEGER or
CHAR(1) datatypes.  

Newbies use BIT or other datatypes to hold the results of a test instead
of the value of an attribute.  The internal state of the database should
be determined predicates each time, so that you get the right answer
instead of just the last value to which the flag was set.  

2) There is no == operator -- this is from 'C'

3) If CurrentDay really means the current date, you can get it with the
CURRENT_TIMESTAMP system expression.

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Setting boolean field from date field values...

Post by Hugh O'Donnel » Wed, 29 Jan 2003 21:19:16


Oops... I know SQL Server doesn't support boolean... I always use bit to
represent them... and still call them boolean... sorry.

As for the code below... thanks!  I was trying to use IF... ELSE, but
that wasn't working.

I normally only do SELECTs from SQL... not updates... so I am not very
good at the syntax.  Thanks for your help.

Hugh

Quote:> SQL Server does not support a BOOLEAN data type. The closest thing to
> a BOOLEAN data is the BIT data type, which can hold a 0 or a 1. Note
> that SQL Server does NOT in any way consider a 0 to be FALSE and a 1
> to be TRUE. Your application can make that interpretation, but your
> application can just equally well make the assumption that 0 is TRUE
> and 1 is FALSE, and SQL Server is not going to care one whit   :-)

> Try something like:

> UPDATE  MyStatus
>     SET CurrentDay = case DateDiff(day, PriceDate, '1/3/2002') when 0
>     then 1
> else 0 end

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

In order to reply via email, please remove the no_spam_for_me_ before
my real email address.