Updating the month in a date time field

[davidbrawn]

I have an SQL database with a date time field but i want to only change the Month part.
ie 10/12/2005 11:04:56 to 10/05/2005 11:04:56

can this be done using UPDATE?
eg.
Table name = tbl_StarterCallData
Field Name = scd_Arrival

Please help urgent.

regards DJ

[liquorvicar]

yes, i believe this can be done:

$sql = "UPDATE tbl_StarterCallData SET scd_Arrival=DATE_ADD(scd_Arrival,INTERVAL 2 MONTH) WHERE ...";

although i haven't tested that.

[mrhappiness]

this code would make 2004-12-15 to 2005-02-15 and so the year was changed too

if that is not wanted:

UPDATE tablename
SET columnname = CONCAT(LEFT(columnname, 5), 5,  SUBSTRING(columnname, 8))

to set the month to may

[liquorvicar]

you're right of course mr happiness. i'm so used to reading US-style dates and transposing them to UK-style dates that my mind saw the 10 and 12 and thought he just wanted to add 2 months to the date!

[mrhappiness]

the funny thing is: i'am almost sure you gave the answer he wanted to have although i gave the answered he asked for 😉

[liquorvicar]

well that's serendipity for you 🙂

[ldtiw]

Ok, ive been needing a similar question answered!!

A couple of extra questions before i try and use this myself are:

1. what date format is DATETIME set as? would it be DD-MM-YYYY or MM-DD-YYYY. Which ever it is (im wanting UK DD-MM-YYYY) is there an easy way to convert it either way.

2. how would i put my database info in there?
   
   its m_upgrade_end in table ipals_members

UPDATE ipals_members
SET m_upgrade_end = CONCAT(LEFT(m_upgrade_end , 5), 5,  SUBSTRING(m_upgrade_end , 8))

Would the above be correct??

Would also need it to add 1 month (or more depending on an option chosen). Its for a personal/profile type site where the user has the option to upgrade for more features ect..

But the number of months would be controled by the quantity. This is passed back to the script as lets say product_quantity. So i would need the number of months to be added taken from the POST of quantity! If thats clear!!!

Also would DATETIME be the best format to use for this kind of thing? if you were also using it to maybe display a count down of how many days they had before the upgrade expires ect....

Thanks,
Danny Lewin

[liquorvicar]

default datetime is YYYY-MM-DD HH:MM:SS as far as i can recall, which is handy for us UK types!

not quite sure what you're after in your second query but it sounds like datetime would be the best field type for you.

[ldtiw]

Ok, so DATETIME would be the best one to you!?!?

If so ill elaborate on the other question!

Heres my table format as it is now, before i try using DATETIME and that piece of code to change the month!

  `m_id` int(9) NOT NULL auto_increment,
  `m_name` varchar(100) NOT NULL default '',
  `m_age` char(3) NOT NULL default '',
  `m_dob_d` char(2) NOT NULL default '',
  `m_dob_m` char(2) NOT NULL default '',
  `m_dob_y` varchar(4) NOT NULL default '',
  `m_county` varchar(50) NOT NULL default '',
  `m_country` varchar(100) NOT NULL default '',
  `m_gender` varchar(20) NOT NULL default '',
  `m_email` varchar(100) NOT NULL default '',
  `m_mail` varchar(100) default 'N',
  `m_ip` varchar(100) NOT NULL default '',
  `m_username` varchar(100) NOT NULL default '',
  `m_password` varchar(100) default '0',
  `m_usertype` varchar(100) NOT NULL default '1',
  `m_upgrade_end` date default NULL,
  `m_joined` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`m_id`)

A user wants to upgrade their membership. They go through the payment gateway which brings them back to a page on my server that i will use to change m_usertype to 2 with 1 being the standard membership.

But i need the m_upgrade_end to change to to date that the upgrade expires on. So if the user upgrades on the 1st Dec 2005, the date will change to 1st Jan 2006.

BUT!!

If they upgrade for a period of say 3 months. The value of this would come from the product_quantity which is passed back to my srcipt from the payment gateway. Each number in the quantity = 1 or more months of upgrade! Is that clear?? Getting myself all confused!

So would need the UPDATE SQL to change to date, adding x number of months which are defined by the passback variable product_quantity.

Hope this is clear? And you approve of the table. Might be a bit messy im sure!

This is the code that update the info. Its made by an Interakt product, so looks a little strange to me!

// make an instance of the transaction object
$tNG1 = new KT_tNG();
// register to a connection
$tNG1->setConnection($main, $database_main);

//set the STARTER condition
$tNG1->registerTrigger(STARTER,'KT_TriggerSTARTER_Default',1,(isset($_POST['u_id']) ? $_POST['u_id'] : null));
$tNG1->registerTrigger(ERROR,"KT_TriggerERROR_ProduceFakeRs",1);
// set the autogeneration information
$tNG1->setTransactionType(UPDATE_TYPE);
$tNG1->setTable('ipals_members');
$tNG1->setColumnsName(Array(('m_id'),('m_usertype'),('m_upgrade_end')));
$tNG1->setColumnsType(Array((NUMERIC_TYPE),(STRING_TYPE),(NUMERIC_TYPE)));
$tNG1->setColumnsValue(Array(((isset($_POST['u_id']) ? $_POST['u_id'] : null)),(2),(0000-00-00)));
$tNG1->setUniqueKey('m_id',(isset($_POST['u_id']) ? $_POST['u_id'] : null),NUMERIC_TYPE);

// execute the transaction
$tNG1->executeTransaction();

Thanks,
Danny

[liquorvicar]

actually a DATE field (as you have already) is probably best unless you're using any of the time elements (i.e. do you check the time of day they have upgraded until?).

you can alter the date like this:

UPDATE table SET date_field=DATE_ADD(date_field,INTERVAL x MONTH) WHERE unique_id=id_to_alter

x is the number of months passed to your script.

my personal comments on your db structure:
m_age - why not make this an UNSIGNED TINYINT field?
m_dob_d,m_dob_m,m_dob_y - do you really need 3 fields for this?? why not a date field?
m_country - you might want to make this contain the standard 2-digit country code and reference to a lookup table.
m_gender - do you need this to be 20 characters long?? how about just CHAR(1) - either M or F
m_mail - see genger above, if this is a straight Y/N. or it could even by a boolean.
m_usertype - does this need to be VARCHAR(100)? or will UNSIGNED TINYINT do?

an UNSIGNED TINYINT field can cope with 0 to 255.

[ldtiw]

No, the times aren't used.

How would i put the "change month" peice of code into the code i posted above?? Looks kinda hard. Thinking it has to go in somewhere here:

$tNG1->setColumnsValue(Array(((isset($_POST['u_id']) ? $_POST['u_id'] : null)),(2),0000-00-00)));

i just thought aswell. The upgrade expire is set to 0000-00-00 by default as its not needed unless they do upgrade. It would need to be the date that they upgrade + x months!!!!!! How would that work?

Just im not all that good at hard coding PHP, just ok at tweeking what DW and extesions create for me!

And just to clarify.. Does it REALLY make a difference which Type you choose when creating a table? And why?

Again.. because of my lack of knowledge. I decided to do that for the date of birth because i dont know how to put and get it both in a form/php nor in the SQL. This might be because im using DW, but probably quite simple to change the code!

ALSO.. Sorry. If i was to use DATE. is there away to change it round to be in a DD-MM-YYYY format??

Thanks,
Danny

[liquorvicar]

i'm not familiar with the class you are using so i don't know if you can insert SQL directly into the values you're passing but you could try:

$tNG1->setColumnsValue(Array(((isset($_POST['u_id']) ? $_POST['u_id'] : null)),(2),DATE_ADD(date_field,INTERVAL x MONTH))));

where date_field is the field you want to update and x is the number of months to upgrade by. or replace date_field with NOW() if you want it to be x months from now.
if that doesn't work, you might want to revert to doing the date handling in PHP rather than SQL using strtotime().

Does it REALLY make a difference which Type you choose when creating a table? And why?

yes, it does make a difference. it might not matter too much on a small database on a small internal application but if you ever want to scale yur application up, you could run into problems. the more data a field could possibly hold the more storage space it needs on disk and hence the slower it is to access that data on the disk (very roughly speaking).

as far as the format of the DATE type, i believe it is fixed in MySQL but there's more than enough date functions in PHP to be able to manipulate it enough.

Just im not all that good at hard coding PHP, just ok at tweeking what DW and extesions create for me!

no offence but i'd start learning if i were you. if you really understand the code you're using, you will be able to tweak it better and quicker, and you will be better prepared to secure your code both from the point of view of data integrity and general security.