12 month Expiry

Hi Guys and Gals,

I require some assistance if possible...

I'm working on a website that deals with 12 month Insurance
Products/Policies and I'm having trouble setting the expiry date.

The obvious (to me) way of doing this would be to simply have an expiry date
of <%=Date()+365%> which in itself works fine.

However, for the sake (seemingly) of pure complication I can only store the
details of the policy in the database (Access) with the day, month and year
separately. (Clients!!!)

I.e. Day: 09 Month 11 Year 2004

Using the date above, the last date of cover would be Day: 08 Month: 12
Year: 2005

Excellent! (I thought), I'll just use <%=Day(Date)-1%>, to reduce the day by
one and <%=Year(Date)+1%> to increase the year by one however it then dawned
on me that if someone was to make an application on the 1st of the month,
this code would display a day number of "0" (eg 1 -1 = 0)

Can anyone please point me in the direction of how I might achieve keeping
the "day", "month", and "year" separate in the database where the start date
is generated automatically as te application date and the end date is
generated automatically as 12 months later?

Thanks

David

You need to look up the DateDiff function, or functions pertaining to ASP
Date().

There are tons of examples on how to do what you're trying to do...although
some explanations are better than others.

David Young
CFWebmasters.com
david (AT) cfwebmasters (DOT) com


"David Burns" <d.burns (AT) dtbinter (DOT) net> wrote

Hi Guys and Gals,

I require some assistance if possible...

I'm working on a website that deals with 12 month Insurance
Products/Policies and I'm having trouble setting the expiry date.

The obvious (to me) way of doing this would be to simply have an expiry
date
of <%=Date()+365%> which in itself works fine.

However, for the sake (seemingly) of pure complication I can only store
the
details of the policy in the database (Access) with the day, month and
year
separately. (Clients!!!)

I.e. Day: 09 Month 11 Year 2004

Using the date above, the last date of cover would be Day: 08 Month: 12
Year: 2005

Excellent! (I thought), I'll just use <%=Day(Date)-1%>, to reduce the day
by
one and <%=Year(Date)+1%> to increase the year by one however it then
dawned
on me that if someone was to make an application on the 1st of the month,
this code would display a day number of "0" (eg 1 -1 = 0)

Can anyone please point me in the direction of how I might achieve keeping
the "day", "month", and "year" separate in the database where the start
date
is generated automatically as te application date and the end date is
generated automatically as 12 months later?

Thanks

David

However, for the sake (seemingly) of pure complication I can only store
the
details of the policy in the database (Access) with the day, month and
year
separately. (Clients!!!)

That's not really the date, then.

You should use an actual date field. Can you not just add a date field?

Excellent! (I thought), I'll just use <%=Day(Date)-1%>, to reduce the day
by
one and <%=Year(Date)+1%> to increase the year by one however it then
dawned
on me that if someone was to make an application on the 1st of the month,
this code would display a day number of "0" (eg 1 -1 = 0)

If that's the case, do the calculation, and if 'day = 0' then you will need
to adjust it so that:
month = month -1

and:
day = (number of days in above chosen month)

That second calculation will be a bit trickier, as each month, obviously,
has a different number of days.

A final option, if you are using ASP, perhaps you can cast the the above 3
fields into one date string?

-Darrel

Hi Darrel

I'd give my right arm to use just one single date field as this is obviously
a lot easier.

The problem with doing this though is this:

At the end of each day all new applications will be emailed across to the
underwriters of the policy by way of a CSV file. I haven't yet got round to
doing the code that will create the CSV file, however I imagined that I
could do it whereby the user could display a list of all new policies and
then click a link to create the CSV file which they can manually email to
the underwriters.

It is the underwriters of the policy who require that the date be split in
to the three separate sections.

This being the case, if I have a date field stored in a database.. is
possible that I can then display the date separated out in to three separate
sections on a web page (I guess by way of a DatePart function?)

If this is possible then I may be able to work around it that way.

Regards

David


"darrel" <notreal (AT) hotmail (DOT) com> wrote

However, for the sake (seemingly) of pure complication I can only store
the
details of the policy in the database (Access) with the day, month and
year
separately. (Clients!!!)

That's not really the date, then.

You should use an actual date field. Can you not just add a date field?

Excellent! (I thought), I'll just use <%=Day(Date)-1%>, to reduce the day
by
one and <%=Year(Date)+1%> to increase the year by one however it then
dawned
on me that if someone was to make an application on the 1st of the month,
this code would display a day number of "0" (eg 1 -1 = 0)

If that's the case, do the calculation, and if 'day = 0' then you will
need
to adjust it so that:
month = month -1

and:
day = (number of days in above chosen month)

That second calculation will be a bit trickier, as each month, obviously,
has a different number of days.

A final option, if you are using ASP, perhaps you can cast the the above 3
fields into one date string?

-Darrel

At the end of each day all new applications will be emailed across to the
underwriters of the policy by way of a CSV file. I haven't yet got round
to
doing the code that will create the CSV file, however I imagined that I
could do it whereby the user could display a list of all new policies and
then click a link to create the CSV file which they can manually email to
the underwriters.

What is a CSV file?

It is the underwriters of the policy who require that the date be split in
to the three separate sections.

They need the data fields split? Or they just need it visually split at the
presentation level?

This being the case, if I have a date field stored in a database.. is
possible that I can then display the date separated out in to three
separate
sections on a web page (I guess by way of a DatePart function?)

Oh...yes...absolutely. Just grab the date field and format it differently
each time it is shown on the page. ASP has a variety of date formatting
tools.

This would be the easiest (and retain the best data integrity) solution.

-Darrel

.oO(darrel)

What is a CSV file?

Comma Separated Values

In such a file each line represents a record, while all fields of a
record are separated by commas. It's a quite usual format for exchanging
tabular data between different applications.

Micha

.oO(David Burns)

I'd give my right arm to use just one single date field as this is obviously
a lot easier.

It's not only easier, it's the correct way to do such things.

Each database is able to properly handle dates and provides useful
functions to deal with them. Doing it on your own with separate fields
is like back-/print-buttons on a website: It's an unreliable reinvention
of the wheel.

This being the case, if I have a date field stored in a database.. is
possible that I can then display the date separated out in to three separate
sections on a web page (I guess by way of a DatePart function?)

Sure. It's possible to store dates correctly in one field and use some
built-in functions in the SELECT statement to return day, month and year
as separate fields.

A short MySQL example (just to get the idea):

mysql> DESCRIBE testDate;
+-------+------+------+-----+---------+-------+

Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
date | date | YES | | NULL | |
+-------+------+------+-----+---------+-------+

mysql> SELECT date FROM testDate;
+------------+

date |
+------------+
2004-11-09 |
+------------+

mysql> SELECT
-> DAYOFMONTH(date) AS day,
-> MONTH(date) AS month,
-> YEAR(date) AS year
-> FROM testDate;
+------+-------+------+

day | month | year |
+------+-------+------+
9 | 11 | 2004 |
+------+-------+------+

Micha