KINDLY HELP:How to combine three columns and form a date and update
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
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
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
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
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
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
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
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
Hi Ester, Id give a try to $myDates = date("Y-m-d\ h:i:s");//Simpler //or update table name set month = date_sub(curdate(), interval 1 month), account_no = '$accNum', acct_type = '$acctType', effective_dt = date_sub(curdate(), interval 29 day), late_fee_to_dt = '$whatever' Or give me time to research and maybe tomorrow I can come up with a solution
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.
@Esther - Not really answering the question here but why would you use two different columns to update the third as opposed to just populating it with the required date in the first place. 0_o?
@Billy - I think you are actually answering the question correctly : those are 4 different writes to the db when only two would have sufficed. Sometimes, complexities are borne of design flaws. -- Ndungi Kyalo biblia.kenya.or.ke On Wed, Jan 27, 2010 at 4:42 PM, Billy <billyx5@gmail.com> wrote:
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.
@Esther - Not really answering the question here but why would you use two different columns to update the third as opposed to just populating it with the required date in the first place. 0_o?
_______________________________________________ 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
@Billy & @Kyalo: So very true fellow skunks, that in addition to having extra storage requirements for the extra columns and an exponentially increasing storage problem when you're doing indexing on some of the columns... @Esther I hope the fields you outlined are not the entire table's schema...where's the PK? :) Trust me it looks scary when you have millions of records. Good db design is crucial. You can make significant storage savings even by just using the tinyint/smallint/mediumint type for some fields depending on your intended usage On Wed, Jan 27, 2010 at 7:52 PM, Ndungi Kyalo <ndungi@gmail.com> wrote:
@Billy - I think you are actually answering the question correctly : those are 4 different writes to the db when only two would have sufficed. Sometimes, complexities are borne of design flaws.
-- Ndungi Kyalo biblia.kenya.or.ke
On Wed, Jan 27, 2010 at 4:42 PM, Billy <billyx5@gmail.com> wrote:
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.
@Esther - Not really answering the question here but why would you use two different columns to update the third as opposed to just populating it with the required date in the first place. 0_o?
_______________________________________________ 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
I'm assuming you're using mysql like the rest of us penguins... update <tablename> set effective_dt = concat(year,'-',month,'-29 00:00:00'); should work. more help is available on mysql docs @ http://dev.mysql.com/doc/refman/5.0/en/ On Wed, Jan 27, 2010 at 2:54 PM, 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
participants (7)
-
Billy -
Brian Ngure -
Clement Ongera -
Haggai Nyang -
Ndungi Kyalo -
Rad! -
wambugu esther