find latest date

madhavr

hello i have columns as below in a table

DateFrom | DateTo

01/01/03 | 31/01/03
01/02/03 | 28/02/03
.....
etc with the dateFrom always being 1st day of month and DateTo is always the last day.

what i need is to find a way of automatically inserting the next months statement dates. so if the latest statement entry was for the month of feb as above then the next entry would be march.

i assume the only way to find the latest statement is to find the lastest entry where say dateTo is the latest entry once i have found that entry i need to follow it up by inserting the next moneths entry as needed.

hope that makes sense!

keith73

actually, I could use a bit more info.

what database are you using (mysql, postgresql, etc.)?
what TYPE are each of the columns (date,int,varchar,etc...)?

keith

madhavr

sorry about that

its a mysql database and the columns are in date format

e.g.

2001-10-01

i think this is american convention or something used by mysql as default

anyway hope that helps

keith73

ok. 2 things

1) you should keep the date format the same as what MySQL uses otherwise it could cause trouble. You can always re-format it after your query.
MySQL only supports YEAR-MONTH-DAY. anything else would not give correct results.
http://www.mysql.com/doc/en/Date_and_time_types.html

2) if you're trying to separate information by month, you don't need 2 date fields to do it. You can structure your queries to select information from a month,year or day.

http://www.mysql.com/doc/en/Date_and_time_functions.html

to reformat the date in PHP
strftime("%d/%m/%y",strtotime($date_column_value));

keith

cgraz

or you could format the date in your actual query.

SELECT DATE_FORMAT(date_field, '%d/%m/%Y') AS formatted_date

Cgraz

madhavr

sorry i forgot to mention that i need two dates becasuse my application handles statements for different periods represented by the date from and dateto so i do need them both.

i dont want to know about formatting thats fine. all i want to know about is how to track down a statement with the latest set of dates so i know from where i need to insert the next latest statement.

for example lets say we have the statements for the periods as below:

january
february
march

PLEASE NOTE IT DOESNT SHOW AS ABOVE IT GOES AS SHOWN ON MY FIRST THREAD WHERE THERE ARE TWO COLUMNS I NEED TO USE THOSE TO WORK OUT WHICH STATEMENT IS THE LATEST ENTRY. THEN ONCE I HAVE DONE THAT I NEED TO DO AN AUTOMATIC INSERT QUERY TO INSERT THE STATEMENT FOR THE NEXT MONTH MAKING SURE I HAVE GOT THE CORRECT LAST DATE FOR THAT MONTH!!

the next due date would be april so it would tell me that march is the latest month so accordingly my application needs to know to insert the dateFrom and dateTo for april.

now does this make sense? hope it does.

thanks

xblue

no need to write all uppercase. you probably know how to get the maximum date out of the DB?

e.g. SELECT MAX(DateTo) FROM table

assign the result to a variable and use it in your insert query

INSERT INTO table (DateFrom, DateTo) VALUES ('$date' + INTERVAL 1 DAY, '$date' + INTERVAL 1 MONTH)

this works fine for me.

(there actually may be ways to do it in one query with some INSERT ... SELECT)