Discussion:
Date Functions?
(too old to reply)
Dan
2005-06-02 15:37:41 UTC
Permalink
What is the equivalent Progress to this SQL Server:

(Invoicedate > = dateadd (m, -3, convert (char(6), getdate(), 112) + '01')
AND (Invoicedate < convert (char (6), getdate(), 112) + '01'))

TIA,
Dan
Flaming Homer
2005-06-02 18:19:05 UTC
Permalink
42 ?
Post by Dan
(Invoicedate > = dateadd (m, -3, convert (char(6), getdate(), 112) + '01')
AND (Invoicedate < convert (char (6), getdate(), 112) + '01'))
TIA,
Dan
Dan
2005-06-02 19:36:34 UTC
Permalink
What?
42 ?
Post by Dan
(Invoicedate > = dateadd (m, -3, convert (char(6), getdate(), 112) + '01')
AND (Invoicedate < convert (char (6), getdate(), 112) + '01'))
TIA,
Dan
Christian Steins
2005-06-21 21:07:52 UTC
Permalink
http://en.wikipedia.org/wiki/The_Answer_to_Life%2C_the_Universe%2C_and_Everything
What?
42 ?
Post by Dan
(Invoicedate > = dateadd (m, -3, convert (char(6), getdate(), 112) + '01')
AND (Invoicedate < convert (char (6), getdate(), 112) + '01'))
TIA,
Dan
Dan
2005-06-21 21:18:22 UTC
Permalink
Thanks. I will have to read that book.
Post by Christian Steins
http://en.wikipedia.org/wiki/The_Answer_to_Life%2C_the_Universe%2C_and_Everything
What?
42 ?
Post by Dan
(Invoicedate > = dateadd (m, -3, convert (char(6), getdate(), 112) + '01')
AND (Invoicedate < convert (char (6), getdate(), 112) + '01'))
TIA,
Dan
Kevin
2005-06-02 22:32:19 UTC
Permalink
42 ?
Class!!! :-D

Kevin
Cervantes
2005-06-06 12:28:46 UTC
Permalink
48
Steve Foley
2005-06-02 20:09:05 UTC
Permalink
Take a look at the following progress functions:

YEAR (D)
MONTH(D)
DAY(D)
DATE(X,Y,Z)

I have no idea what a dateadd function, or a convert function does. I'm
guseeing getdate() is current date.

What are you trying to accomplish?
Post by Dan
(Invoicedate > = dateadd (m, -3, convert (char(6), getdate(), 112) + '01')
AND (Invoicedate < convert (char (6), getdate(), 112) + '01'))
TIA,
Dan
CF
2005-06-03 12:58:00 UTC
Permalink
All the "SQL Server" functions mentioned here are carryovers from
Sybase, and are fully T-SQL (Transact-SQL).

Progress offers a few equivalents, but not all.

T-SQL "getdate()" is equivalent to Progress "cur_date()".

T-SQL CONVERT() is a bit more complicated, but not necessary in your
statement, I think. Anyway, Progress has a function "TO_CHAR()" that
takes a date and a format string as arguments. I don't have my TSQL
references in front of me, so I don't remember what format "112" equates
to, but here's an example of TO_CHAR():

SELECT TO_CHAR( CUR_DATE(), 'MM/DD/YYYY' );

yields: "06/03/2005".


As far as T_SQL's ultra-useful "DATEADD()", Progress has only
"ADD_MONTHS()". There is no ADD_DAYS() or ADD_YEARS(), but in this
case, it looks like you're trying to add months anyway (or subtract).
It's a very simple syntax like "ADD_MONTHS( date, 6 )". And I'm only
guessing that Progress, like Sybase, doesn't puke when you pass a
negative value.

So without actually taking the time to test this stuff, I'd say the
PROGRESS equivalent of your original statement would be:

(( invoicedate >= ADD_MONTHS( CUR_DATE(), -3 ) AND
( invoicedate < CUR_DATE() ))

Again, I don't see the purpose of your CONVERT() function. And no
database engine is going to know how to do date comparisons on character
dates.
Post by Steve Foley
YEAR (D)
MONTH(D)
DAY(D)
DATE(X,Y,Z)
I have no idea what a dateadd function, or a convert function does. I'm
guseeing getdate() is current date.
What are you trying to accomplish?
Post by Dan
(Invoicedate > = dateadd (m, -3, convert (char(6), getdate(), 112) + '01')
AND (Invoicedate < convert (char (6), getdate(), 112) + '01'))
TIA,
Dan
Dan
2005-06-03 14:10:50 UTC
Permalink
The CONVERT changes the date to CHAR(6) in the format YYYYMMDD, so the
DD is truncated. Then +'01' concatenates so we have YYYYMM01, the first
for the month. Your progress SQL is not for the first of the month.

Dan
Post by CF
All the "SQL Server" functions mentioned here are carryovers from
Sybase, and are fully T-SQL (Transact-SQL).
Progress offers a few equivalents, but not all.
T-SQL "getdate()" is equivalent to Progress "cur_date()".
T-SQL CONVERT() is a bit more complicated, but not necessary in your
statement, I think. Anyway, Progress has a function "TO_CHAR()" that
takes a date and a format string as arguments. I don't have my TSQL
references in front of me, so I don't remember what format "112" equates
SELECT TO_CHAR( CUR_DATE(), 'MM/DD/YYYY' );
yields: "06/03/2005".
As far as T_SQL's ultra-useful "DATEADD()", Progress has only
"ADD_MONTHS()". There is no ADD_DAYS() or ADD_YEARS(), but in this
case, it looks like you're trying to add months anyway (or subtract).
It's a very simple syntax like "ADD_MONTHS( date, 6 )". And I'm only
guessing that Progress, like Sybase, doesn't puke when you pass a
negative value.
So without actually taking the time to test this stuff, I'd say the
(( invoicedate >= ADD_MONTHS( CUR_DATE(), -3 ) AND
( invoicedate < CUR_DATE() ))
Again, I don't see the purpose of your CONVERT() function. And no
database engine is going to know how to do date comparisons on character
dates.
Post by Steve Foley
YEAR (D)
MONTH(D)
DAY(D)
DATE(X,Y,Z)
I have no idea what a dateadd function, or a convert function does. I'm
guseeing getdate() is current date.
What are you trying to accomplish?
Post by Dan
(Invoicedate > = dateadd (m, -3, convert (char(6), getdate(), 112) + '01')
AND (Invoicedate < convert (char (6), getdate(), 112) + '01'))
TIA,
Dan
Dan
2005-06-03 14:13:10 UTC
Permalink
To determine the date range for the last 3 full months. It works like a
charm with SQL Server.
Post by Steve Foley
YEAR (D)
MONTH(D)
DAY(D)
DATE(X,Y,Z)
I have no idea what a dateadd function, or a convert function does. I'm
guseeing getdate() is current date.
What are you trying to accomplish?
Post by Dan
(Invoicedate > = dateadd (m, -3, convert (char(6), getdate(), 112) + '01')
AND (Invoicedate < convert (char (6), getdate(), 112) + '01'))
TIA,
Dan
Steve Foley
2005-06-05 19:02:40 UTC
Permalink
So if today is June 5, you want the date range to be March 1 to May 31?

The last day of last month would be

today - day ( today ).

The first day of three months prior to above date would be

date ( today - month ( today ) -65 , 1 , year ( today - day ( today ) -
65 ) )
Post by Dan
To determine the date range for the last 3 full months. It works like a
charm with SQL Server.
Post by Steve Foley
YEAR (D)
MONTH(D)
DAY(D)
DATE(X,Y,Z)
I have no idea what a dateadd function, or a convert function does. I'm
guseeing getdate() is current date.
What are you trying to accomplish?
Post by Dan
(Invoicedate > = dateadd (m, -3, convert (char(6), getdate(), 112) + '01')
AND (Invoicedate < convert (char (6), getdate(), 112) + '01'))
TIA,
Dan
Loading...