Equation Evaluation - SQL Server Forums

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

SQL Server Development (2000)

Equation Evaluation

Author

Topic

ackweb
Yak Posting Veteran

54 Posts

Posted - 2004-10-01 : 02:00:22

Is there a way to flag a subset of a query which meets a certain criteria. For example, the query is all the organizations:

SELECT
OrgID,
OrgName,
StartDate

FROM Accounts

ORDER BY OrgID

However, I'd also like to flag all the organizations that meet a particular criteria, such as (StartDate BETWEEN '9/1/04' AND '10/1/04'). Is there a way to use a CASE statement or something else to do this within the larger query? Thanks in advance for any guidance on this?

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-01 : 04:40:44

SELECT OrgID, OrgName, StartDate
, CASE StartDate > '9/1/04' and StartDate < '10/1/04' then 1 else 0 end as FLAG
FROM Accounts
ORDER BY OrgID

Hemanth Gorijala
BI Architect / DBA...

ackweb
Yak Posting Veteran

54 Posts

Posted - 2004-10-01 : 08:07:39

I tried this, but I'm getting the following error:
"Line 4: Incorrect syntax near '>'."

SELECT
OrgID,
OrgName,
(CASE StartDate > '9/1/04' AND StartDate < '10/1/04' THEN 1 ELSE 0 END) AS Flag

FROM Accounts

ORDER BY OrgID

If is use BETWEEN, then I get this error:
"Incorrect syntax near the keyword 'BETWEEN'."

CASE StartDate BETWEEN '9/1/04' AND '10/1/04' THEN 1 ELSE 0 END AS Flag

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-10-01 : 08:12:17

Is '9/1/04' The '9-Jan-2004' or '1-Sept-2004'? SQL can't work out what dates you mean, it is expecting a datetime and gettign a varchar...

Use convert(datetime,'9/1/04') and it should be fine...

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-10-01 : 08:17:37

You can also use ISO format for dates i.e. CCYYMMDD

steve

To alcohol ! The cause of - and solution to - all of life's problems

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-01 : 08:20:54

[code]SELECT OrgID,
OrgName,
StartDate,
Flag =
CASE When Startdate between '9/1/04' and '10/1/04' then 1
else 0
end
FROM Accounts
ORDER BY OrgID[/code]

Your case syntax was not correct, causing you to get the error.

-Jon
Now a "Yak Posting Veteran".

ackweb
Yak Posting Veteran

54 Posts

Posted - 2004-10-01 : 08:33:59

I've tried both of the following modifications and still get this error:
"Incorrect syntax near '>'."

CASE (StartDate > convert(datetime,'9/1/04') AND StartDate < convert(datetime,'10/1/04')) THEN 1 ELSE 0 END AS SetUp,

CASE (StartDate > '040901' AND StartDate < '041001') THEN 1 ELSE 0 END AS SetUp,

It appears that SQL automatically converts the VarChar syntax to DateTime, because this WHERE clause works:

SELECT StartDate FROM account WHERE StartDate BETWEEN '9/1/04' and '9/30/04'

What am I doing wrong here?

ackweb
Yak Posting Veteran

54 Posts

Posted - 2004-10-01 : 08:36:38

That did the trick! Thanks "surefooted", you "Yak Posting Veteran".

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-10-01 : 08:42:09

In ISO format dates translate as follows

date 29 September 2004 would be 20040929
i.e. CC = 20, YY = 04 MM = 09 (must be padded with a 0 for months Jan - Sep) and DD = 29

So for date 1st September 2004 you would get 20040901

I always think of it as being backwards but it is very useful e.g. for sorting filenames if you have the date at the start of the file name

Also I believe that it is more efficient to use DATEDIFF for date comparisons though in this case it would probably seem uneccessarily complicated

steve

To alcohol ! The cause of - and solution to - all of life's problems

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources