Nah, that muddles it all up. Here's the solution methinks:

update <tablename> set effective_dt = concat(year,'-',month,'-',if(mod(year,4) <> 0 && month = 2,'28','29'),' 00:00:00');


On Wed, Jan 27, 2010 at 3:38 PM, Haggai Nyang <haggai.nyang@gmail.com> wrote:
Hahaha...smoked too much PHP and Flex I think.

Switch the 28 and 29 text values, that should do the trick...



On Wed, Jan 27, 2010 at 3:36 PM, Haggai Nyang <haggai.nyang@gmail.com> wrote:
Very true Rad...an oversight:

This pushes last day to 28 for all Feb leap years.

update <tablename> set effective_dt = concat(year,'-',month,'-',if(mod(year,4) = 0 && month = 2,'28','29'),' 00:00:00');

Haven't tried on a db though...


On Wed, Jan 27, 2010 at 3:13 PM, Rad! <conradakunga@gmail.com> wrote:
That design is asking for trouble. Not all months have 29 days every year. It will crash with a non leap year.

Personally i'd store a date the first time.

Failing which use another date - the first - instead of the 29th


On Wed, Jan 27, 2010 at 3:11 PM, Brian Ngure <brian@mystique.boldlygoingnowhere.org> wrote:
Hi Esther,

You can try using the MAKETIME MySQL function (if you are using
MySQL). MSSQL has a similar function.

On Wed, Jan 27, 2010 at 3:06 PM, wambugu esther <essiekui@gmail.com> wrote:
> Thanks Ndungi,
> this is in SQL ,yes there is a reason as to why the month and year occupy
> the separate column,the two columns for month and year will be used to
> populate the effective_date column,the datetime can be in any format most
> preferable date-month-year.
>
> thanks
>
>
> On Wed, Jan 27, 2010 at 3:01 PM, Ndungi Kyalo <ndungi@gmail.com> wrote:
>>
>> Esther,
>>
>> In which language would you like the script done ? Secondly, is there a
>> specific reason why 'month' and 'year' would occupy their own separate
>> columns ? the `datetime` type already accounts for the year-month-date
>> hr::min:ss .
>> --
>> Ndungi Kyalo
>> biblia.kenya.or.ke
>>
>>
>>
>>
>> On Wed, Jan 27, 2010 at 11:54 AM, wambugu esther <essiekui@gmail.com>
>> wrote:
>>>
>>> Hi skunkworks,
>>> kindly help me
>>> i have a table with  these columns
>>> (
>>> month int  null,
>>> year  int not null,
>>> acct_no char(15) not null,
>>> acct_type char(10) not null,
>>> effective_dt datetime null,
>>> late_fee_to_dt)
>>>
>>> the field effective_dt is null and i want it to be populate by a value
>>> which d=should be composed of '29' then the respective value of column month
>>> and respective value of column year,
>>> kindly help me do a script to update the effective_dt.
>>>
>>> will appreciate your assistance
>>> --
>>>
>>>
>>> Best regards
>>> Esther Wambugu
>>>
>>>
>>> _______________________________________________
>>> Skunkworks mailing list
>>> Skunkworks@lists.my.co.ke
>>> http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks
>>> ------------
>>> Skunkworks Server donations spreadsheet
>>>
>>> http://spreadsheets.google.com/ccc?key=0AopdHkqSqKL-dHlQVTMxU1VBdU1BSWJxdy1fbjAwOUE&hl=en
>>> ------------
>>> Skunkworks Rules
>>> http://my.co.ke/phpbb/viewtopic.php?f=24&t=94
>>> ------------
>>> Other services @ http://my.co.ke
>>> Other lists
>>> -------------
>>> Announce:
>>> http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks-announce
>>> Science:  http://lists.my.co.ke/cgi-bin/mailman/listinfo/science
>>> kazi:     http://lists.my.co.ke/cgi-bin/mailman/admin/kazi/general
>>
>>
>> _______________________________________________
>> Skunkworks mailing list
>> Skunkworks@lists.my.co.ke
>> http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks
>> ------------
>> Skunkworks Server donations spreadsheet
>>
>> http://spreadsheets.google.com/ccc?key=0AopdHkqSqKL-dHlQVTMxU1VBdU1BSWJxdy1fbjAwOUE&hl=en
>> ------------
>> Skunkworks Rules
>> http://my.co.ke/phpbb/viewtopic.php?f=24&t=94
>> ------------
>> Other services @ http://my.co.ke
>> Other lists
>> -------------
>> Announce:
>> http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks-announce
>> Science:  http://lists.my.co.ke/cgi-bin/mailman/listinfo/science
>> kazi:     http://lists.my.co.ke/cgi-bin/mailman/admin/kazi/general
>
>
>
> --
>
>
> Best regards
> Esther Wambugu
>
>
> _______________________________________________
> Skunkworks mailing list
> Skunkworks@lists.my.co.ke
> http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks
> ------------
> Skunkworks Server donations spreadsheet
> http://spreadsheets.google.com/ccc?key=0AopdHkqSqKL-dHlQVTMxU1VBdU1BSWJxdy1fbjAwOUE&hl=en
> ------------
> Skunkworks Rules
> http://my.co.ke/phpbb/viewtopic.php?f=24&t=94
> ------------
> Other services @ http://my.co.ke
> Other lists
> -------------
> Announce: http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks-announce
> Science:  http://lists.my.co.ke/cgi-bin/mailman/listinfo/science
> kazi:     http://lists.my.co.ke/cgi-bin/mailman/admin/kazi/general
>



--
Regards,

Brian Ngure


_______________________________________________
Skunkworks mailing list
Skunkworks@lists.my.co.ke
http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks
------------
Skunkworks Server donations spreadsheet
http://spreadsheets.google.com/ccc?key=0AopdHkqSqKL-dHlQVTMxU1VBdU1BSWJxdy1fbjAwOUE&hl=en
------------
Skunkworks Rules
http://my.co.ke/phpbb/viewtopic.php?f=24&t=94
------------
Other services @ http://my.co.ke
Other lists
-------------
Announce: http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks-announce
Science:  http://lists.my.co.ke/cgi-bin/mailman/listinfo/science
kazi:     http://lists.my.co.ke/cgi-bin/mailman/admin/kazi/general