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-dHlQVTMxU1VBdU1BSWJxdy1f... > ------------ > 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-dHlQVTMxU1VBdU1BSWJxdy1f...
------------ 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-dHlQVTMxU1VBdU1BSWJxdy1f...
------------ 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-dHlQVTMxU1VBdU1BSWJxdy1f... ------------ 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-dHlQVTMxU1VBdU1BSWJxdy1f... ------------ 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