Conditional Where - 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 2005 Forums

Transact-SQL (2005)

Conditional Where

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-08 : 07:59:28

Ken writes "I am looking to find a simple solution to the problem as follows:

I have a date field and I want to select the latest item in the table , I am doing this using datepart(dy, myFieldDate) =< datepart(dy, @CurrentDate). This works except that when the year changes my latest item (myFieldDate) is say dy = 360 and it is now dy = 1 .

So I need something to perform the following SQl / Where Clause

Declare @CurrentDate DateTime
Select @CurrentDate = GetDate()

Select Top 1
theDate,
theTitle

From dbo.Subject

Where
IF NOT NULL (datepart(dy, theDate)<= datepart(dy, @CurrentDate)and Enable = @Enable)
Else IF
((datepart(yyyy, theDate)-1)<= datepart(yyyy, @CurrentDate) AND
(datepart(dy, theDate)<= datepart(dy, @CurrentDate) and Enable = @Enable)

"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 08:33:15

select * from Subject
where thedate = (select max(thedate) from subject)

Peter Larsson
Helsingborg, Sweden

Stix
Starting Member

1 Post

Posted - 2007-01-08 : 19:33:01

Thank you for your response - just to be sure I am on the same page

If I do select max - I will encounter the problem of Future dates being in the system (thedate can be future present or past) - Will select max; only select the date (past or present closest to the Current Date ?? I believe the max is only used to select like the highest value. However; I think if I were to use this changing your code slightly it would work correctly - I also do not wish use the time portion of date field - only the true date ddmmyyyy - which is why I was using Day of Year.. will this ignore time parameters ?

select * from Subject
where thedate = (select max(thedate) from subject Where thedate <= @currentdate)

quote:
Originally posted by Peso

select * from Subject
where thedate = (select max(thedate) from subject)

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 19:44:03

[code]select *
from Subject
where thedate = (select max(theDate) from subject Where thedate < dateadd(day, datediff(day, 0, getdate()), 1))[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 19:45:13

Please provide some sample data from your table.
Also post your expected output based on the provided sample data.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources