Combining Two Date Fields in a view..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Combining Two Date Fields in a view..

Author

Topic

msp
Starting Member

7 Posts

Posted - 2005-03-17 : 09:46:59

I am trying to combine two date fields to one boolean field in a view.

What I am attempting to do is mark the new field,"SoftwareConfirmed" as true if either "Date_TO_Confirmed" or "Date_BO_Confirmed" has a date in it.

I have the following code.

quote:
SELECT Server_Name, Date_TO_Confirmed, Date_BO_Confirmed, CAST(Date_BO_Confirmed AS bit) | CAST(Date_TO_Confirmed AS bit)
AS SoftwareConfirmed
FROM tbl_data_server

This code seems to be close expect it gives an positive result if both Date_TO_Confirmed, Date_BO_Confirmed have a date rather than either.

Thanks in advance...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-17 : 09:50:30

try using case:
case when Date_BO_Confirmed is not null or Date_BO_Confirmed is not null then 1 else 0 end

Go with the flow & have fun! Else fight the flow

msp
Starting Member

7 Posts

Posted - 2005-03-17 : 11:27:26

Sorry I am not quite sure what you mean.. I thought case just changes from upper to lower (and vise versa).
I think I need to convert the Nulls to 0. (Although not sure how)...

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-17 : 12:00:14

It's no wonder why you're confused about CASE...that's Oracle syntax

If they are date fields why are you converting them....oh, I see...

That's not a good, or possible(is it) idea....

Just look up DECODE

Brett

8-)

msp
Starting Member

7 Posts

Posted - 2005-03-17 : 13:12:21

The reson why I am trying to decode them is that I am adding an advancement to the database (having a date rather than a yes/no flag) but do not want the current user queries to stop working after the upgrade...

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-17 : 13:24:44

Co, my point being that you canno convert a date to bit without DECODE...

This is a sql server forum btw...

You might want to look here

http://www.dbforums.com/f4

But you want something Like

DECODE(Column,NULL,COALESCE(bit,0),Column,NOT NULL, COALESCE(bit,1))

You also might want to bookmark this

http://otn.oracle.com/pls/tahiti/tahiti.docindex

Brett

8-)

msp
Starting Member

7 Posts

Posted - 2005-03-18 : 05:19:41

I know it is a SQL server I am using SQL server not Oracle with an access front end...

msp
Starting Member

7 Posts

Posted - 2005-03-18 : 06:01:54

spirit1, Brett - Thanks for the help. I needed to use COALESCE.

Statement below is the statement i used and it produces the results I want...

quote:
GO
Set Nocount off
Go

SELECT Server_Name, Date_TO_Confirmed, Date_BO_Confirmed,
CAST(COALESCE(Date_BO_Confirmed,Date_TO_Confirmed) AS bit)

AS SoftwareConfirmed

FROM tbl_data_server

Thanks again.

Toby

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources