[support] How to store/retreive date fields into/from MySql

[Carl Wiedemann]

I assume you're using Drupal 6. See the date_api.module functions like
date_make_date() which will return a Date object that can be formatted with
date_format_date().

Your schema type should be DATETIME or DATE
http://dev.mysql.com/doc/refman/5.0/en/datetime.html

You probably want to have something like this to INSERT/UPDATE the db.

$date_array['year'] . '-' . $date_array['month'] . '-' . $date_array['day']
. ' 00:00:00';

Then, when you SELECT the value you can do something like this.

$displayed_date = date_format_date(date_make_date($value, 'UTC'), 'custom',
'm/d/Y');

Non-language specific strings do not need to be handled with t(), too. :)

Also, is there a reason you're not using Content Profile with Date module to
accomplish your task?
http://drupal.org/project/content_profile
http://drupal.org/project/date


On Mon, Mar 28, 2011 at 10:49 PM, Austin Einter <austin.einter at gmail.com>wrote:

> After all search, I found it really difficult to deal with date. I am
> trying to store the date in MySql table as varchar type and placeholder as
> %s.
>
> However I need two functions to convert date from array format to string
> format (required while storing into table) and string format to array format
> (Required while showing it in form).
>
> The below code does that for me.
>
>
> *function dateArrToStr(&$customDateArr)
> {
>  $strDate = t($customDateArr['day']).t('-
> ').t($customDateArr['month']).t('-') . t($customDateArr['year']);
>  return $strDate;
> }*
> **
> **
> *function dateStrToArr (&$customDateStr, $length)
> {
>     $convDate = array();
>  $index = 0;
>  $customToken = t('');
>  $dashPosition = 0;
>
>  for($index = 0; $index < $length; $index++)
>  {
>   if($customDateStr[$index] == t('-'))
>   {
>    if($dashPosition == 0)
>    {
>     //Set the day
>     $dashPosition++;
>        $convDate['day'] =  $customToken;
>    }
>    else if ($dashPosition == 1)
>    {
>     //Set the month
>     $dashPosition++;
>     $convDate['month'] = $customToken;
>    }
>    $customToken = t('');
>   }
>   else
>   {
>    $customToken = t($customToken) . t($customDateStr[$index]) ;
>   }
>  }
>  //Set year
>  $convDate['year'] = $customToken;
>  return $convDate;
> }*
>
>
> Regards
> Austin
>
> On Mon, Mar 28, 2011 at 8:36 AM, Austin Einter <austin.einter at gmail.com>wrote:
>
>> Hi All
>> I have a date field in my form.
>> How do I store/retreive the value into/from MySQL.
>>
>> On google search, I saw people are suggesting to use either "int" or
>> "datetime" type.
>> Please suggest which one needs to be used.
>>
>> If I use int (I hope core uses int), while storing can I just
>> store "form['values']['date1']" as %d and while retreivin, what are the
>> related apis to convert back to date.
>>
>> Regards
>> Austin.
>>
>>
>>
>
>
> --
> [ Drupal support list | http://lists.drupal.org/ ]
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/support/attachments/20110328/67c7bf64/attachment-0001.html