Hmm, Travis fails on PHP 5.4 for some reason. Looks quite unrelated to my change here. Anyone has a clue why?

Thank you for this! I've applied this patch. Here's what I've found.

What happens now is that when I leave the "Finish Publishing" field blank, it stays blank. Upon hovering over the blank "Finish Publishing" field on the edit screen (back-end), it shows the value "Monday 01 January 1900." However, when I leave this field blank for an article that is therefore listed as "Published and Current," it appears on the front end -- but is indicated to be "Expired."

The "Expired" label is on the front-end, and appears even when I'm a guest (not logged into the site in the front end).

Thanks, I think I saw where it originated from.
Can you test the updated PR? It should now use the database nulldate as well in frontend.

Looking at the code where a hardcoded check is used it may also be an issue in the user form/profile where it shows the last visited date. Can you check that?

I've made the updates as listed under the tab on this page, "Files changed" (thank you for those), but I've refreshed the homepage and still see "Expired." Upon editing the article item, I can see that the "Finish Publishing" field is blank as it should be, but hovering over, the tooltip still says "Monday, 01 January 1900."

Sorry for not mentioning this earlier, but I can see that this also applies to modules, when edited from the back-end -- however modules do not have any warning label of "expired" on them.

I've checked the profile pages, and their edit pages on both front-end and back-end views, and do not see any issues or inconsistencies in the "Last visited date" field.

I've investigated a bit further and it appears "$this->item->publish_down" returns "1900-01-01 00:00:00.000" while "JFactory::getDbo()->getNullDate()" returns "1900-01-01 00:00:00." Does this help?

Upon updating "libraries\joomla\database\query\sqlsrv.php" line 39 to include the ".000" in the protected variable, the "expired" problem goes away. I also updated "libraries\joomla\database\driver\sqlsrv.php" in the same way.

I've investigated a bit further and it appears "$this->item->publish_down" returns "1900-01-01 00:00:00.000" while "JFactory::getDbo()->getNullDate()" returns "1900-01-01 00:00:00." Does this help?

Yes, that would explain it. So MS SQL does store miliseconds? Maybe the getNullDate method should then be adjusted... I have to see how easy that would be.

I've made the updates as listed under the tab on this page

Just as an info, you can use the patchtester component to apply a PR. See http://joom.la/patchtester for the component. The "Releases" tab contains the packages file which can be downloaded and installed as a regular Joomla installation. Once installed, you can select a PR from the list and apply it with a single click.

Upon updating "libraries\joomla\database\query\sqlsrv.php" line 39 to include the ".000" in the protected variable, the "expired" problem goes away. I also updated "libraries\joomla\database\driver\sqlsrv.php" in the same way.

Updated PR with this suggestion. I think it makes sense. Please test and make sure nothing breaks elsewhere.

Nothing seems to be broken as a result of adding milliseconds to those files; however, it looks like fields are again defaulting to 1970-01-01 00:00:00 on blank dates (for both "created" and "finish publishing" date fields).

Next try
Tooltips should no longer show and the dates should hopefully no longer default to 1970-01-01.
Also found a bug when creating a new item, because then the value is 0 and not the nulldate.

Just a thought: Wouldn't it be simpler if we had typed JQuery (our php object) fields, and that php-side a null date would always have the same format, as it's really the database layer's responsibility to map the php domain into the database's own domain ? How are Doctrine and other DBALs handling that ? Or more simpler we pick the MySQL null date format and all other database drivers adapt in the statements, similar with how we do it for #__ but for reads too ? That would solve incompatibilities not only in the core but also in the extensions. Of course a setting should be able to disable that feature if the software has been adapted. As said was just a thought. It is still better to aim for a proper solution, and the current effort is a cleaner solution than the automatic substitution.

I agree that this would be the most elegant solution.
I'm just not familiar enough with how our database drivers work to know if it's even possible to do.
In the end it may become more complicate than just checking against JFactory::getDbo()->getNullDate().

I've applied the updates made and the site seems to no longer have any issues related to the dates... the dates are saving properly and the front-end does not show "expired" any longer. Thank you.

Currently, if I leave "Start Publishing" blank, it stays blank; should it not default to the current date in that case? Thank you.

Currently, if I leave "Start Publishing" blank, it stays blank; should it not default to the current date in that case? Thank you.

Yes, it should take the current date if the form is sent with an empty value there. However I don't see how that would be related to the database nulldate. If the field is empty, it will send an empty string and this will be the same for any database.
The check is done here: https://github.com/joomla/joomla-cms/blob/staging/administrator/components/com_content/models/article.php#L205. Imho that should work.

Hi there. Upon further testing, I have found more problems with this issue.

Upon clicking "New" in the article manager, I see that the dates listed in the form are as follows:

Start Publishing: 1970-01-01 00:00:00
Finish Publishing: 1970-01-01 00:00:00
Created Date: 1970-01-01 00:00:00

and

Modified Date: 1970-01-01 00:00:00

When I save the new item, the Modified Date is updated to the current datetime. However the other three items stay the same, and the article is listed as published but expired.

Please view the attached image which shows what I'm talking about.

Hope this helps. Thanks.

Is this as a result of this patch or is this a behaviour which existed already prior to this patch?

Sorry, I was mistaken. I refreshed the browser and it seems to be working now. Sorry!

Thanks, so you don't have any issues left on MS SQL related to this PR?
Then it would be helpful if someone could test it on MySQL to see that nothing broke there.

and postgres

I've tested this with creating a new module, as well as a new weblink item. I believe it's been resolved. Thank you!

I got error when click save on new article (on MSSSQL) but on MySQL is work fine.

Error

Save failed with the following error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'WHERE'.SQL=SELECT * FROM ( SELECT id, parent_id, level, lft, rgt , ROW_NUMBER() OVER (ORDER BY (select 0)) AS RowNumber FROM kb9dj_assets) _myResults WHERE RowNumber BETWEEN 1 AND 1 WHERE id = 27


This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/3803.

@joomlacorner Does saving work when the patch isn't applied? Afaik it shouldn't have any impact on the savong queri. So it may well be a different issue not related to this patch.

@Bakual Yes, saving work well when the patch isn't applied.

@joomlacorner Sounds strange because the query doesn't have any date stuff in it and this PR only touches date things.

test: Installed testing data on postgres on staging. articles show as expired. applied patch. articles no longer show as expired and edit button is also changed to the non-expired version. Can still save articles in the backend without any issues. Result: success

test: I tested current staging with mysql and postgresql. When I create an article and hit the save button I get:
mysql:

• modified date: = created date -> in prior versions the modified data was null
• modified by: null postgresql:
• modified date: ":character varying
• modified by: null

After "save & exit" the "modified date" in postgresql gets set to the current date and "modified by" is set to the user currently logging in.
When "modified date" is set "modified by" should also be set - in my view. Or nothing should be done at all, as it was in the past. I don´t know where the "character varying (postgres installation) comes from. But saving the article does function anyway.

@Bakual What do you make of the test results by @waader ?

This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/3803.

Honestly I have no clue yet where that comes from.

@Bakual I removed the test results so the issues of @waader and @joomlacorner can be investigated and we can get some new tests done.

This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/3803.

Where are we with this?
Without this being committed for 3.4 once again we will be making a release that can not work with all the supported databases

Raising priority according to https://docs.joomla.org/Bug_and_Issue_Tracker_Priority

Imho it should work as is. I have no clue where the issues from @waader are coming from. It may well be unrelated issues which just surfaced because of this PR.

Two different people reported the mssql issues. We need someone with it who can look into the issue for us before we can merge this imho :(

We have two harsh options
1. Commit this now so that at least postgres is working in 3.4
2. Ignore this now - postgres will be broken again in 3.4 but mssql works

Personally I would go with option 1. If the MS SQL users dont help testing etc...

The only change I can imagine which is related to MS SQL and storing articles would be the added miliseconds to the getNullDate method of MS SQL. I could remove that for now and then it should still work for PostgreSQL (since nothing changes for that) and MS SQL should work again as bad as it does today. There should be no SQL errors anymore, at least I can't imagine where they would come from.
Would that be an approach?
After that one could make a PR to solve the MS SQL issue with the added miliseconds.

Go for it or we will be stuck forever (or until PLT get some windows test servers)

Reverted the commit which added the miliseconds to the MS SQL getNullDate method.
Hopefully it works better now.

Also resolved a conflict and rebased with staging.

This PR has been committed in commit b4a0cde but I forgot to add the # to the PR number.

Commit 65ee68b fixes #3803

@roland-d It will never say "merged" if you close it from a commit. That's because you didn't merge it
It will instead say @roland-d closed this pull request from a commit x hours ago and roland-d closed this in abcdefg x hours ago
The PR will show as Closed with unmerged commits for the PR owner.

That is fine and expected since you took the changes, squashed them and commited them yourself.

The issue for MS SQL do to milliseconds(.000) never got solved?

It should be. Since Joomla 3.4.0.

From what I can see its not. https://github.com/joomla/joomla-cms/blob/3.4.0/libraries/joomla/database/driver/sqlsrv.php#L46
and not in master either https://github.com/joomla/joomla-cms/blob/master/libraries/joomla/database/driver/sqlsrv.php#L46

Or am I missing something?

Ah you're right. I had to revert that part due to some issues I couldn't solve. Didn't remember that.
Feel free to attempt to fix it. I can't test it myself so I could only shoot into the dark and hope for the best.

Sure, I have done the change in our test environment and it seems to run whit out any problems. Do you remember what the issue where?

#3803 (comment) I'd guess it's the one in this comment