basicall I want if the ActiveDate <= getdate() (as in today is after the
item was active) then the active flag is a bit 1, else its a bit 0... I
tried this as a flat out <= statement, got an error by SQL Managment
studio... so how would this be done? thanks!
Don't use Management Studio's "design" view to enter the formula. Try using
a query window, e.g.
ALTER TABLE dbo.MyTable ADD Flag AS CONVERT(BIT, CASE WHEN ActiveDate <=
GETDATE() THEN 1 ELSE 0 END);
However it would be bette to perform this calculation in a view and query
that, instead of "storing" the data, IMHO.
A
"Smokey Grindle" <nos...@nospam.com> wrote in message
news:%23Rnwu1w...@TK2MSFTNGP02.phx.gbl...
This would be done by forgetting all the basic rules for programming
in SQL :)
1) We do not use the proprietary BIT data type that does not exist in
Standard SQL
2) Flags are for assembly language programming and have no place in
RDBMS; that is why SQL has no BOOLEAN data types
3) We do not store computed columns in a table. Write a VIEW and
learn to use CURRENT_TIMESTAMP instead of the old proprietary
getdate() function call to get today's active data.
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:1184087662....@22g2000hsm.googlegroups.com...
Why a +1 and not a -1? Have you compared VB and C# on this issue? Or
any of the X3J languages? Since SQL uses 3VL, where is your UNKNOWN
and how do you move it to an application.
In the RDBMS world, true and false are at a "higher level" than the
data -- they refer to predicates about that data and are not part of
it.
An RDBMS person designs data, not just queries. You will quickly find
that you need a status code that can expand as "yes/no" becomes "yes/
no/not answered/ impossible because of other answers/ etc." Or you
have one attribute split into "20-questions" with a ton of constraints
to try and co-ordinate them.
>> as for other SQL why would I care about that right now either if I am targeting my program at SQL server only? <<
Professionalism? Maintainable and readable code? "Future Proofing"?
That last one is from Ken Henderson; it means we known that SQL Server
will always be moving toward Standards and not away from them, so
let's avoid problems before they hit us. For example, the bad
programmers who used BIT got caught when the data type went from (0/1)
to (0/1/NULL) as an exact numeric data type.
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:1184093745.5...@57g2000hsv.googlegroups.com...
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:1184093745.5...@57g2000hsv.googlegroups.com...
It is there for historial reasons. Sybase/SQL Server grew up before
the Standards and was written for 16 bit hardware on a UNIX platform
decades ago.
The main purpose today, now that we understand RDBMS better, is to
lock you into their products.
I do not care about .NET and ADO, or other proprietary front ends for
applications. I am the database guy and I write an RDBMS that can
work with any front end.
So why do you continually harp on about the (no longer existent)
differences in interpretation of bit/boolean by VB and C#? Surely you
shouldn't be worrying about such issues?
Damien