# Revision history [back]

Alter Table "MYTABLE" Alter Column "DATEFIELD" set default '2016-12-31'

Alter Table "MYTABLE" Alter Column "DATEFIELD" set default '2016-12-31'
'2016-12-31' Edit 12/02/2016 Based upon @Quarto Die comment, I did some further testing.  The testing was done using an embedded DB, a split DB and a MySQL DB.  The only field type of concern here is a Date

 type. If a table is created without a default date, the ALTER statement above works time after time. Embedded DB - If a default date is declared, it gets screwed up unless you use the format of YYYY-MM-DD (see this post). Any other format gets really messed up. The ALTER statement executes but has no effect on the default. The answer is to delete the default by editing the table. Then the ALTER statement works (although you can't view it by editing the table - another bug). Split DB - If a default date is declared, it gets screwed up unless you use the format of YYYY-MM-DD (same post). Any other format gets really messed up. The ALTER statement executes but has no effect on the default. The ONLY answer found is to delete the field by editing the table and adding the field again without the default. Then the ALTER statement works (although you can't view it by editing the table - another bug). If the table is created outside of LO Base (in this case used SQL Workbench/J) there was NO problem with an initial default date and the ALTER statement worked. MySQL DB - If a default date is declared, it gets screwed up unless you use the format of YYYY-MM-DD (same post). Any other format gets really messed up. The ALTER statement executes but has no effect on the default. The ONLY answer found is to delete the field by editing the table and adding the field again without the default. Then the ALTER statement works (although you can't view it by editing the table - another bug). If the table is created outside of LO Base (in this case used MySQL Workbench) there was NO problem with an initial default date and the ALTER statement worked. So the problem always occurs, and the ALTER statement has no affect (even with YYYY-MM-DD format), if the default is set in the original design of the table. The problem definitely lies within LO Base. In the next day or two I will check bug reports and if none, submit one.

 3 No.3 Revision updated 2016-12-03 05:40:33 +0200 Alter Table "MYTABLE" Alter Column "DATEFIELD" set default '2016-12-31' Edit 12/02/2016 Based upon @Quarto Die comment, I did some further testing. The testing was done using an embedded DB, a split DB and a MySQL DB. The only field type of concern here is a Date type. If a table is created without a default date, the ALTER statement above works time after time. Embedded DB - If a default date is declared, it gets screwed up unless you use the format of YYYY-MM-DD (see this post). Any other format gets really messed up. The ALTER statement executes but has no effect on the default. The answer is to delete the default by editing the table. Then the ALTER statement works (although you can't view it by editing the table - another bug). Split DB - If a default date is declared, it gets screwed up unless you use the format of YYYY-MM-DD (same post). Any other format gets really messed up. The ALTER statement executes but has no effect on the default. The ONLY answer found is to delete the field by editing the table and adding the field again without the default. Then the ALTER statement works (although you can't view it by editing the table - another bug). If the table is created outside of LO Base (in this case used SQL Workbench/J) there was NO problem with an initial default date and the ALTER statement worked. MySQL DB - If a default date is declared, it gets screwed up unless you use the format of YYYY-MM-DD (same post). Any other format gets really messed up. The ALTER statement executes but has no effect on the default. The ONLY answer found is to delete the field by editing the table and adding the field again without the default. Then the ALTER statement works (although you can't view it by editing the table - another bug). If the table is created outside of LO Base (in this case used MySQL Workbench) there was NO problem with an initial default date and the ALTER statement worked. So the problem always occurs, and the ALTER statement has no affect (even with YYYY-MM-DD format), if the default is set in the original design of the table. The problem definitely lies within LO Base. In the next day or two I will check bug reports and if none, submit one. Edit #2 12/02/2016 - Have further info on default values. When using Base to create and set the default for a table field, it is stored in the content.xml file of the .odb while using an external source, the default is stored in the database itself. What this means is with an embedded DB you can simply edit the table and remove the default. Then use the ALTER statement to set a new default. With split or external DB, open the .odb with an archive manager, open the content.xml file with a text editor, find the faulty date (format will look correct) and delete the date leaving the quote marks intact. Then the ALTER statement works (tested). 4 No.4 Revision updated 2016-12-03 07:11:51 +0200 Alter Table "MYTABLE" Alter Column "DATEFIELD" set default '2016-12-31' Edit 12/02/2016 Based upon @Quarto Die comment, I did some further testing. The testing was done using an embedded DB, a split DB and a MySQL DB. The only field type of concern here is a Date type. If a table is created without a default date, the ALTER statement above works time after time. Embedded DB - If a default date is declared, it gets screwed up unless you use the format of YYYY-MM-DD (see this post). Any other format gets really messed up. The ALTER statement executes but has no effect on the default. The answer is to delete the default by editing the table. Then the ALTER statement works (although you can't view it by editing the table - another bug). Split DB - If a default date is declared, it gets screwed up unless you use the format of YYYY-MM-DD (same post). Any other format gets really messed up. The ALTER statement executes but has no effect on the default. The ONLY answer found is to delete the field by editing the table and adding the field again without the default. default(see below) Then the ALTER statement works (although you can't view it by editing the table - another bug). If the table is created outside of LO Base (in this case used SQL Workbench/J) there was NO problem with an initial default date and the ALTER statement worked. MySQL DB - If a default date is declared, it gets screwed up unless you use the format of YYYY-MM-DD (same post). Any other format gets really messed up. The ALTER statement executes but has no effect on the default. The ONLY answer found is to delete the field by editing the table and adding the field again without the default. default(see below). Then the ALTER statement works (although you can't view it by editing the table - another bug). If the table is created outside of LO Base (in this case used MySQL Workbench) there was NO problem with an initial default date and the ALTER statement worked. So the problem always occurs, and the ALTER statement has no affect (even with YYYY-MM-DD format), if the default is set in the original design of the table. The problem definitely lies within LO Base. In the next day or two I will check bug reports and if none, submit one. Edit #2 12/02/2016 - Have further info on default values. When using Base to create and set the default for a table field, it is stored in the content.xml file of the .odb while using an external source, the default is stored in the database itself. What this means is with an embedded DB you can simply edit the table and remove the default. Then use the ALTER statement to set a new default. With split or external DB, open the .odb with an archive manager, open the content.xml file with a text editor, find the faulty date (format will look correct) and delete the date leaving the quote marks intact. Then the ALTER statement works (tested). 5 No.5 Revision updated 2016-12-03 16:21:03 +0200 Alter Table "MYTABLE" Alter Column "DATEFIELD" set default '2016-12-31' Edit 12/02/2016 Based upon @Quarto Die comment, I did some further testing. The testing was done using an embedded DB, a split DB and a MySQL DB. The only field type of concern here is a Date type. If a table is created without a default date, the ALTER statement above works time after time. Embedded DB - If a default date is declared, it gets screwed up unless you use the format of YYYY-MM-DD (see this post). Any other format gets really messed up. The ALTER statement executes but has no effect on the default. The answer is to delete the default by editing the table. Then the ALTER statement works (although you can't view it by editing the table - another bug). Split DB - If a default date is declared, it gets screwed up unless you use the format of YYYY-MM-DD (same post). Any other format gets really messed up. The ALTER statement executes but has no effect on the default. The ONLY answer found is to delete the field by editing the table and adding the field again without the default(see below) Then the ALTER statement works (although you can't view it by editing the table - another bug). If the table is created outside of LO Base (in this case used SQL Workbench/J) there was NO problem with an initial default date and the ALTER statement worked. MySQL DB - If a default date is declared, it gets screwed up unless you use the format of YYYY-MM-DD (same post). Any other format gets really messed up. The ALTER statement executes but has no effect on the default. The ONLY answer found is to delete the field by editing the table and adding the field again without the default(see below). Then the ALTER statement works (although you can't view it by editing the table - another bug). If the table is created outside of LO Base (in this case used MySQL Workbench) there was NO problem with an initial default date and the ALTER statement worked. So the problem always occurs, and the ALTER statement has no affect (even with YYYY-MM-DD format), if the default is set in the original design of the table. The problem definitely lies within LO Base. In the next day or two I will check bug reports and if none, submit one. Edit #2 12/02/2016 - Have further info on default values. When using Base to create and set the default for a table field, it is stored in the content.xml file of the .odb while using an external source, the default is stored in the database itself. What this means is with an embedded DB you can simply edit the table and remove the default. Then use the ALTER statement to set a new default. With split or external DB, open the .odb with an archive manager, open the content.xml file with a text editor, find the faulty date (format will look correct) and delete the date leaving the quote marks intact. Then the ALTER statement works (tested). Edit 12/03/2016: Bug report submitted Bug 104375. 


 Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license. ⬆ to top impressum (legal info) | about | faq | help | privacy policy | give feedback Powered by Askbot version 0.9.3 var _paq = _paq || []; _paq.push(["disableCookies"]); _paq.push(["trackPageView"]); _paq.push(["enableLinkTracking"]); (function() { var u=(("https:" == document.location.protocol) ? "https" : "http") + "://piwik.documentfoundation.org/"; _paq.push(["setTrackerUrl", u+"piwik.php"]); _paq.push(["setSiteId", "55"]); var d=document, g=d.createElement("script"), s=d.getElementsByTagName("script")[0]; g.type="text/javascript"; g.defer=true; g.async=true; g.src=u+"piwik.js"; s.parentNode.insertBefore(g,s); })(); Please note: AskLibO requires javascript to work properly, please enable javascript in your browser, here is how //IE fix to hide the red margin var noscript = document.getElementsByTagName('noscript')[0]; noscript.style.padding = '0px'; noscript.style.backgroundColor = 'transparent'; askbot['urls']['mark_read_message'] = '/en/s/messages/markread/'; askbot['urls']['get_tags_by_wildcard'] = '/en/s/get-tags-by-wildcard/'; askbot['urls']['get_tag_list'] = '/en/s/get-tag-list/'; askbot['urls']['follow_user'] = '/followit/follow/user/{{userId}}/'; askbot['urls']['unfollow_user'] = '/followit/unfollow/user/{{userId}}/'; askbot['urls']['user_signin'] = '/en/account/signin/'; askbot['urls']['getEditor'] = '/en/s/get-editor/'; askbot['urls']['translateUrl'] = '/en/s/translate-url/'; askbot['urls']['apiGetQuestions'] = '/en/s/api/get_questions/'; askbot['urls']['ask'] = '/en/questions/ask/'; askbot['urls']['questions'] = '/en/questions/'; askbot['settings']['groupsEnabled'] = false; askbot['settings']['static_url'] = '/m/'; askbot['settings']['minSearchWordLength'] = 4; askbot['settings']['mathjaxEnabled'] = false; askbot['settings']['sharingSuffixText'] = '#Ask_LIbreOffice'; askbot['settings']['errorPlacement'] = 'after-label'; askbot['data']['maxCommentLength'] = 1000; askbot['settings']['editorType'] = 'markdown'; askbot['settings']['commentsEditorType'] = 'rich\u002Dtext'; askbot['messages']['askYourQuestion'] = 'Ask Your Question'; askbot['messages']['acceptOwnAnswer'] = 'accept or unaccept your own answer'; askbot['messages']['followQuestions'] = 'follow questions'; askbot['settings']['allowedUploadFileTypes'] = [ "jpg", "jpeg", "gif", "bmp", "png", "tiff", "odt", "ods", "odp", "odg", "odc", "odf", "odi", "odm", "ott", "ots", "otp", "otg", "odb", "doc", "docx", "xls", "xlsx", "ppt", "pptx" ]; askbot['data']['haveFlashNotifications'] = true; askbot['data']['activeTab'] = 'questions'; askbot['settings']['csrfCookieName'] = 'ask.libreoffice.org_en_csrf'; askbot['data']['searchUrl'] = ''; /*<![CDATA[*/ $('.mceStatusbar').remove();//a hack to remove the tinyMCE status bar$(document).ready(function(){ // focus input on the search bar endcomment var activeTab = askbot['data']['activeTab']; if (inArray(activeTab, ['users', 'questions', 'tags', 'badges'])) { var searchInput = $('#keywords'); } else if (activeTab === 'ask') { var searchInput =$('#id_title'); } else { var searchInput = undefined; animateHashes(); } var wasScrolled = $('#scroll-mem').val(); if (searchInput && !wasScrolled) { searchInput.focus(); putCursorAtEnd(searchInput); } var haveFullTextSearchTab = inArray(activeTab, ['questions', 'badges', 'ask']); var haveUserProfilePage =$('body').hasClass('user-profile-page'); if ((haveUserProfilePage || haveFullTextSearchTab) && searchInput && searchInput.length) { var search = new FullTextSearch(); askbot['controllers'] = askbot['controllers'] || {}; askbot['controllers']['fullTextSearch'] = search; search.setSearchUrl(askbot['data']['searchUrl']); if (activeTab === 'ask') { search.setAskButtonEnabled(false); } search.decorate(searchInput); } else if (activeTab === 'tags') { var search = new TagSearch(); search.decorate(searchInput); } if (askbot['data']['userIsAdminOrMod']) { $('body').addClass('admin'); } if (askbot['settings']['groupsEnabled']) { askbot['urls']['add_group'] = "/en/s/add-group/"; var group_dropdown = new GroupDropdown();$('.groups-dropdown').append(group_dropdown.getElement()); } var userRep = $('#userToolsNav .reputation'); if (userRep.length) { var showPermsTrigger = new ShowPermsTrigger(); showPermsTrigger.decorate(userRep); } }); if (askbot['data']['haveFlashNotifications']) {$('#validate_email_alert').click(function(){notify.close(true)}) notify.show(); } var langNav = $('.lang-nav'); if (langNav.length) { var nav = new LangNav(); nav.decorate(langNav); } /*]]>*/$(document).ready(function() { var toTop=$("#toTop"); if ($(window).scrollTop() == 0) { toTop.fadeOut(1000); } $(window).scroll(function() { if ($(this).scrollTop() != 0) { toTop.fadeIn(); } else { toTop.fadeOut(); } }); toTop.click(function(event) { event.preventDefault(); $("html,body").animate({scrollTop: 0},800); }); }); //todo - take this out into .js file$(document).ready(function(){ $('div.revision div[id^=rev-header-]').bind('click', function(){ var revId = this.id.substr(11); toggleRev(revId); }); lanai.highlightSyntax(); }); function toggleRev(id) { var arrow =$("#rev-arrow-" + id); var visible = arrow.attr("src").indexOf("hide") > -1; if (visible) { var image_path = '/m/default/media/images/expander-arrow-show.gif?v=20'; } else { var image_path = '/m/default/media/images/expander-arrow-hide.gif?v=20'; } image_path = image_path + "?v=20"; arrow.attr("src", image_path); \$("#rev-body-" + id).slideToggle("fast"); } for (url_name in askbot['urls']){ askbot['urls'][url_name] = cleanUrl(askbot['urls'][url_name]); }