I am making a web based program for our billing and I am having problems coming up with how to send a bill to all users that are due between to dates. here is what I have.
I have a field in my MySQL Database that holds the date the user is currently billed through it is called "billed_through" the data is in this format 04102003 or month day year.
Now how would I in eather php or MySQL select all users that have a "billed_through" that is between 04102003 and 06102003 or April 10, 2003 and June 10, 2003?
I am using mktime in PHP to create the time before I put it into the database but I can't think of how to select between to dates as you are actually working with 3 variables (day, month, year)
Any help on this would be appreciated, I know there must be a simple solution to this.I don't know what the data type for a date time stamp is in mySql. In transact sql (sql server) the data type is either dateTime or smallDateTime. The following statement should be ANSI sql compliant:
select current_timeStamp
and the result would be:
2003-04-10 23:16:43.357
or something to the like. To select between date ranges you can use the dateDiff() function. For instance
select * from databaseName.owner.tableName
where dateDiff(dd, billDUEdate, '2003-04-15') >=1 and
dateDiff(dd, billDUEdate, '2003-04-30')<=1
the above query attempts to retreive records that have a billDUEdate inbetween the 15th and 30th of april, 2003.
Keep in mind that dateDiff holds three parameters. I have used it here to measure the difference in days, as represented by a dd. The result may be a positive or negative integer value.well that pointed me in the right direction thank you, the select statement I used for MySQL in case anyone else wants to know was
select * from user_accounts where billed_through < "2003-04-14";
And that seems to do everything I need, I was trying to do too much in php and didn't think about letting MySQL do it. Thank you.
I have a field in my MySQL Database that holds the date the user is currently billed through it is called "billed_through" the data is in this format 04102003 or month day year.
Now how would I in eather php or MySQL select all users that have a "billed_through" that is between 04102003 and 06102003 or April 10, 2003 and June 10, 2003?
I am using mktime in PHP to create the time before I put it into the database but I can't think of how to select between to dates as you are actually working with 3 variables (day, month, year)
Any help on this would be appreciated, I know there must be a simple solution to this.I don't know what the data type for a date time stamp is in mySql. In transact sql (sql server) the data type is either dateTime or smallDateTime. The following statement should be ANSI sql compliant:
select current_timeStamp
and the result would be:
2003-04-10 23:16:43.357
or something to the like. To select between date ranges you can use the dateDiff() function. For instance
select * from databaseName.owner.tableName
where dateDiff(dd, billDUEdate, '2003-04-15') >=1 and
dateDiff(dd, billDUEdate, '2003-04-30')<=1
the above query attempts to retreive records that have a billDUEdate inbetween the 15th and 30th of april, 2003.
Keep in mind that dateDiff holds three parameters. I have used it here to measure the difference in days, as represented by a dd. The result may be a positive or negative integer value.well that pointed me in the right direction thank you, the select statement I used for MySQL in case anyone else wants to know was
select * from user_accounts where billed_through < "2003-04-14";
And that seems to do everything I need, I was trying to do too much in php and didn't think about letting MySQL do it. Thank you.