Informix Date Function Equivalent
Informix Date Function Equivalent
am 20.05.2005 17:14:47 von 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.
Re: Informix Date Function Equivalent
am 20.05.2005 18:01:28 von 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
--
Re: Informix Date Function Equivalent
am 20.05.2005 19:06:37 von matt
The DATE function takes as input a non-DATE value such as CHAR,
DATETIME, or INTEGER and returns the corresponding DATE value.
Re: Informix Date Function Equivalent
am 20.05.2005 19:46:36 von 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)))))))
Re: Informix Date Function Equivalent
am 21.05.2005 00:23:50 von 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.