function date 3

[codrutza]

Hello, please help me with this:
I need in a select to write
select * from table where
table.ReadyDate<date from current month, first day (now, < 1 May 2009)

 

[hmckillop]

select * from table where table.ReadyDate< 
DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

"I'm living so far beyond my income that we may almost be said to be living apart

 

[willdevelope]

select * from table
where table.ReadyDate= dateadd(day,-day(getdate( ))
1,getdate( ))

 

[codrutza]

Thank you.
I need help with another one.

select * from table where
mydate< dateadd(day,-day(getdate( ))1,getdate( ))

mydate is the result of:

If table.type=’imp’
Then
(If table.veseelarrdate is null then table.arrdate
else table.vesselarrdate)

else If table.type=’exp’
Then
(If table.veseldepdate is null then table.depdate
else table.vesseldepdate)

 

[markros]

select * from table where
(case when table.type = 'imp' then COALESCE(table.VeseelArrDate, Table.ArrDate) else coalesce(table.VesselDepDate,Table.DepDate) end) < dateadd(day,-day(getdate( ))1,getdate( ))

Not sure if this would work as is, we may want to use instead

select * from (select *, (case when table.type = 'imp' then COALESCE(table.VeseelArrDate, Table.ArrDate) else coalesce(table.VesselDepDate,Table.DepDate) end) myDate from table) T where T.myDate < dateadd(day,-day(getdate( ))1,getdate( ))

 

[codrutza]

Thank you, Markros

I can't understand this:
(table.VesselDepDate,Table.DepDate) end) myDate from table) T where T.myDate

I need help with beneath:

…..
and
(
CASE WHEN Table.Type='Imp' then
COALESCE(Table.VesselArrDate,Table.ArrDate)
ELSE COALESCE(Table.VesselDepDate,Table.DepDate)
END
)
<dateadd(day,-day(getdate()),getdate())


and I need to write somehow this too

and
(
CASE WHEN Table.Type='Imp' then
COALESCE (year(Table.VesselArrDate),year(Table.ArrDate))
ELSE COALESCE(year(Table.VesselDepDate),year(Table.DepDate))
……=@year
END
)

@year is parameter

 

[markros]

I think in your case it would be better to create columns as columns of the derived table instead of trying to implement them directly in where clause. However, the major question is - would there be a penalty of doing this in two steps (derived table and then where applied vs. direct complex where)?

Anyone have an experience here?