MS SQL statement translated into Informix
I'm looking for a little help converting a MS SQL statement into Informix
syntax. This is what the MS SQL statement looks like:
Declare @YesterdaysDate INT
Set @YesterdaysDate = (Cast( LTrim( Str( DatePart( yy, GetDate()) - 1900))
+ LTrim( Str( DatePart( mm, GetDate())))
+ LTrim( Str( DatePart( dd, GetDate()))) as Int))-1
select table1.day1
from table1
where table1.day1=@YesterdaysDate
order by table1.day1
The query works great in Informix without the Declare / Set statement and
using a real integer in the 'where' statement, i.e. where
table1.day1=991215
Thanks.
Regards...RD
RDawson wrote:
>
> I'm looking for a little help converting a MS SQL statement into Informix
> syntax. This is what the MS SQL statement looks like:
>
> Declare @YesterdaysDate INT
> Set @YesterdaysDate = (Cast( LTrim( Str( DatePart( yy, GetDate()) - 1900))
> + LTrim( Str( DatePart( mm, GetDate())))
> + LTrim( Str( DatePart( dd, GetDate()))) as Int))-1
> select table1.day1
> from table1
> where table1.day1=@YesterdaysDate
> order by table1.day1
> The query works great in Informix without the Declare / Set statement and
> using a real integer in the 'where' statement, i.e. where
> table1.day1=991215
Informix SQL does not have variables so you would have to do this either
as a stored procedure (Informix's Stored Procedure Language (SPL) does
have variables) or through a front-end programming tool like I4GL, R4GL,
CLI/C, or ESQL/C. With a stored procedure you could can the entire
query
or write a generic function to return the date as an integer in the
format you need it and call the proc from the where clause. A more
Informix'ish way to accomplish this might be:
SELECT table1.day1
FROM table1
WHERE table1.day1 =
(((year(TODAY) MOD 100) * 10000) + month(TODAY) * 100 + day(TODAY))
ORDER BY table1.day1;
BTW shouldn't you be using four digit dates in that column calculation
by now? Y2k is just 14 days away. There's still time to add 19,000,000
to every record to prepare.
Art S. Kagel
Making some assumptions about MS SQL, you could do the following :
select table1.day1 from table1
where table1.day1 = (case when (year(today-1) <= 1999)
then year(today -1) - 1900
else year(today -1) - 2000
end) || month (today -1) || day (today -1);
The above assumes that the year 2000 dates would be stored in table1 as 101
for Jan 1st and 1231 for Dec 31st, 2000 - otherwise, modify the case
statement accordingly.
BTW, your MS SQL statements appear to have problems associated with the 1st
of each month. For example, if "today" had been the 1st of December, 1999,
@YesterdaysDate would have been set to 991200. Or have I made some wrong
assumptions?
Rudy
RDawson wrote:
> I'm looking for a little help converting a MS SQL statement into Informix
> syntax. This is what the MS SQL statement looks like:
>
> Declare @YesterdaysDate INT
> Set @YesterdaysDate = (Cast( LTrim( Str( DatePart( yy, GetDate()) - 1900))
> + LTrim( Str( DatePart( mm, GetDate())))
> + LTrim( Str( DatePart( dd, GetDate()))) as Int))-1
> select table1.day1
> from table1
> where table1.day1=@YesterdaysDate
> order by table1.day1>
> The query works great in Informix without the Declare / Set statement and
> using a real integer in the 'where' statement, i.e. where
> table1.day1=991215
>
> Thanks.
>
> Regards...RD