Informix Date Function Equivalent

[Matt]

Hello. I have an Informix SQL statement that I need to run in MS SQL
Server. When I try to execute it I get the following error message from
Query Analyzer:

Server: Msg 195, Level 15, State 10, Line 4
'date' is not a recognized function name.

Can anyone help me convert this informix sql statement into and MS Sql
Statement? Here is the query I have:

select
a.comp_code,
a.comp_date,
case when date(date(date(comp_date - day(comp_date) +1) - 2 units
month) - 1 units day) < b.inception_date then b.inception_date
else date(date(date(comp_date - day(comp_date) +1) - 2 units month) - 1
units day)
end prior_date,
a.net_return,
a.net_uv,
a.gross_return,
a.gross_uv,
a.estimated_flag
from composite_perf a, composite_detail b
where
(month(a.comp_date) in (3,6,9,12) or a.comp_date = b.inception_date)
and a.comp_code = 'AEU'
and a.comp_code = b.comp_code
into #tmp_composite_data

Thanks in advance.

[David Portas]

It might have helped if you'd told us what DATE actually does here. It
looks like date arithmetic so take a look at DATEADD and DATEDIFF in
Books Online.

--
David Portas
SQL Server MVP
--

[Matt]

The DATE function takes as input a non-DATE value such as CHAR,
DATETIME, or INTEGER and returns the corresponding DATE value.

[Matt]

Figured it out using the DATEADD function.

case when
DateAdd(day,-1,(DateAdd(month,-2,(DateAdd(day,1,(DateAdd(day,-day(comp_date),comp_date)))))))
< b.beg_date then b.beg_date
else
DateAdd(day,-1,(DateAdd(month,-2,(DateAdd(day,1,(DateAdd(day,-day(comp_date),comp_date)))))))

[Ross Presser]

On 20 May 2005 10:06:37 -0700, Matt wrote:

The DATE function takes as input a non-DATE value such as CHAR,
DATETIME, or INTEGER and returns the corresponding DATE value.

The equivalent of this would be CONVERT:

convert(datetime,'2005-05-14')

But you'd still need dateadd/datediff to do the date arithmetic.

One of the interesting, if convoluted, uses for convert with dates is to
group by an unusual time period. For instance, this would produce a summary
by 6-hour shifts:

SELECT ShiftStart, count(*)
FROM (
SELECT convert(datetime,
floor(convert(float, EntryTime) * 4) / 4) as ShiftStart
FROM Entries
WHERE EntryTime IS NOT NULL
) AS RoundedEntries
GROUP BY ShiftStart
ORDER BY ShiftStart

I tried using 3 instead of 4, but ran into rounding errors: instead of
16:00:00 I was getting 15:59:59.997.