Dates

Dates

Post by S.L.Sarg.. » Fri, 14 Jul 1995 04:00:00



I have a problem with dates in an ABF application using Ingres 6.4/03 on UNIX.

There are four fields defined as follows:-

1-3 :- ints containing day,month,year (e.g. 13,7,1995).
4   :- An Ingres date field.

Does anyone know if there is a function that can take the three seperate
integers and convert them into the standard Ingres date format
(e.g. 13-jul-1995) or is there some other way of doing it like a 3gl 'C'
routine for instance.

Regards.

Steve.

|--------------------------------------------------------------------------|
| Steve Sargent, Vox +44 171 775 3220, Fax +44 171 775 3221                |
| QMW Computing Services,  Mile End Road,  London E1 4NS, UK               |

| WWW page: http://www.qmw.ac.uk/~cgaa160/user.html                        |
|--------------------------------------------------------------------------|

 
 
 

Dates

Post by cpco.. » Fri, 14 Jul 1995 04:00:00


Quote:>There are four fields defined as follows:-

>1-3 :- ints containing day,month,year (e.g. 13,7,1995). 4   :- An Ingres
date  
>field.

>Does anyone know if there is a function that can take the three seperate  
>integers and convert them into the standard Ingres date format (e.g.  
>13-jul-1995) or is there some other way of doing it like a 3gl 'C' routine
for  
>instance.

Try (this is un-nested; you can write it as a single nested function and
sacrifice readability):

   date_string := varchar(dd) + '-';

   date_string := date_string +
       left( shift ('JanFebMarAprMayJunJulAugSepOctNovDec', -3*(mm - 1) ),
3);

   date_string := date_string + '-' + varchar(1900 + yy);

   ABF_date := date(date_string);  

This worked for me, a few days ago.  
--
Charles P. Cohen, Cyborg Consultants


 
 
 

Dates

Post by Tim Holm » Fri, 14 Jul 1995 04:00:00



>Does anyone know if there is a function that can take the three seperate
>integers and convert them into the standard Ingres date format
>(e.g. 13-jul-1995) or is there some other way of doing it like a 3gl 'C'
>routine for instance.

Try
SELECT
   date(varchar(i1)+'/'+varchar(i2)+'/'+varchar(i3)) from yourtable

Tim.

 
 
 

Dates

Post by ZETO-ROD » Sat, 15 Jul 1995 04:00:00


: I have a problem with dates in an ABF application using Ingres 6.4/03 on UNIX.

: There are four fields defined as follows:-

: 1-3 :- ints containing day,month,year (e.g. 13,7,1995).
: 4   :- An Ingres date field.

: Does anyone know if there is a function that can take the three seperate
: integers and convert them into the standard Ingres date format
: (e.g. 13-jul-1995) or is there some other way of doing it like a 3gl 'C'
: routine for instance.

: Regards.

: Steve.

: |--------------------------------------------------------------------------|
: | Steve Sargent, Vox +44 171 775 3220, Fax +44 171 775 3221                |
: | QMW Computing Services,  Mile End Road,  London E1 4NS, UK               |

: | WWW page: http://www.qmw.ac.uk/~cgaa160/user.html                        |
: |--------------------------------------------------------------------------|

How about date(char(f1)+'/'+char(f2)+'/'+char(f3)) ?

Tomasz Bogucki

 
 
 

Dates

Post by John Litt » Sun, 16 Jul 1995 04:00:00


date(varchar(month)+'-'+varchar(day)+'-'+varchar(year))

says...

Quote:

>I have a problem with dates in an ABF application using Ingres 6.4/03 on
UNIX.

>There are four fields defined as follows:-

>1-3 :- ints containing day,month,year (e.g. 13,7,1995).
>4   :- An Ingres date field.

>Does anyone know if there is a function that can take the three seperate
>integers and convert them into the standard Ingres date format
>(e.g. 13-jul-1995) or is there some other way of doing it like a 3gl 'C'
>routine for instance.

Try

    date(varchar(month)+'-'+varchar(day)+'-'+varchar(year))

How the date function interprets its argument depends on how you've got
II_DATE_FORMAT set; from memory any setting does not stop a date with
hyphens and a numeric month being interpreted as an American date.  If
you know how II_DATE_FORMAT will be set you may prefer to replace (or
remove) the hyphens with the punctuation according to your setting, and
adjust the expression to put the components in the right order.

The settings available for II_DATE_FORMAT have always annoyed me, because
I can't set it to something that I regard as friendly to my users.  
Outside the U.S. we want all dates on input to be interpreted as day,
month, year, (or perhaps year, month, day) *regardless* of the
punctuation the user has supplied.  If it's set to MULTINATIONAL, for
example, and the user types 020495, they get the fourth of February, an
*idiot* American date (from a non-american user's perspective).  I'd like
a setting that always has the month in the middle.  Users want often
dates to be displayed in the default 03-Feb-1901 format, but to enter
them with the minimum number of keystrokes, 30201 for this example.  We
generally settle for the GERMAN setting at non-VMS sites, because it
accepts 30201.  The output is ugly in ISQL or QBF without a form but in
reports or ABF we set a template to get the nice output.  However, get a
temp in to do some data entry, and lots of dates in the first 12 days of
a month will be entered with day and month transposed, because they typed
them with a slash.

Perhaps someone knows a better way, or there's some new settings I don't
know about.  Of course, the above applies to Ingres clients only.

--
John Little
Auckland, New Zealand, phone (64) (9) 575 4621

 
 
 

1. ((Current Date - Start Date)/(End Date - Start Date))*Contract Amt

We have a cube consisting of a fact table with a measure of Contract Amt ($)
plus some other accounting numbers related to projects and a couple of
dimensions (actually more but that should be enough information for this
question) - Time and Projects with a project number as a key.

We are trying to create the following calculated member:

((Current Date - Start Date)/(End Date - Start Date))*Contract Amt

Where

  a.. Current Date is a member of Time dimension (either default or
explicitly created)
  b.. Start and End Dates are properties of a project number (should they be
defined as custom properties or some other way?)

What is the correct syntax for this calculation?

Can it be a member of measures dimension?

Thanks much for any helpJ

Alex Pitko

2. Status of Applications in oas 4.0.7 are "down"

3. SQL: Select some data with date criteria, but only by Date part of date field

4. Looking for expert with Sybase on NetWare

5. Dates, Dates, & More Dates

6. Recommended reading...

7. DATE DATE DATE Filemaker and Month names

8. DTS Fixed Width Text File

9. VarChar date to datetime without the date

10. Extracting date formats from a date

11. Convert date time to date?

12. Days +Date into Date Format

13. date in query (date to int)