U4-2055 - Unpublish Date Bug in Umbraco 6.0.3

Created by Sören Deger 05 Apr 2013, 11:58:17 Updated by Sebastiaan Janssen 05 Jun 2015, 16:28:23

Is duplicated by: U4-2442

Relates to: U4-2433

If I set the "Remove at" date at today (2013-04-05) to 2013-05-04 00:00 and save or save & publish the node, the node is unpublished!

I have tested this in a lot of Umbraco 4.7.+ websites on 4 independent servers and it works perfect. Only in all of my Umbraco 6.0.3 websites on all of this servers are the same bug.

I have install a new empty 6.0.3 without any packages on another webserver. I have created one document type and have created one content Node. I have the same bug. You must reload the nodetree, wait round about 1 Minute and reload the nodetree again: Now the node is unpublished...

Seems to be a date formating issue. 2013-05-04 means the 4th of May, but maybe, it is interpreted as 5th of April, this would explain the behavior. YYYY-MM-DD <> YYYY-DD-MM.

This may be caused by the German language? May perhaps anyone else reproduce the error? And how can I fix this?

Here is the topic in our.umbraco: http://our.umbraco.org/forum/core/general/39862-Unpublish-Date-Bug-in-Umbraco-603

Best regards Sören

Comments

Ian 11 Jul 2013, 09:57:12

I am experiencing similar behaviour, with the dates being reversed - but looking correct in SQL Studio... This is with UK region and settings, where we have DD/MM/YYYY

This code here: public IEnumerable GetContentForExpiration() { using (var repository = _repositoryFactory.CreateContentRepository(_uowProvider.GetUnitOfWork())) { var query = Query.Builder.Where(x => x.Published == true && x.ExpireDate <= DateTime.Now); var contents = repository.GetByQuery(query);

            return contents;
        }
    }

The Query for today (11th July) comes out as [cmsDocument].[published] = 1 AND [cmsDocument].[expireDate] <= '07/11/2013 10:27:52'

< '07/11/2013 10:27:52' passed into SQL in that form will return everything before 7th of November!!


Sebastiaan Janssen 11 Jul 2013, 10:20:24

@Sören You REALLY should update to at least 6.0.6 because of the severe security issues mentioned here: http://umbraco.com/follow-us/blog-archive/2013/5/1/security-update-two-major-vulnerabilities-found.aspx (but I recommend upgrading to 6.1.2 now, it's a straight-forward upgrade with no breaking changes).

@Ian is that also in v6.0.3? If not, which version please?


Ian 11 Jul 2013, 10:23:01

Hi Sebastiaan, I should have mentioned - this is happening for me in Umbraco 6.1.1 and SQL Server 2005.


Sören Deger 11 Jul 2013, 10:54:59

Hi Jan, after upgrade on 6.1.2 we have the same issue (with SQL Server 2008). I've raised the priority, because it's a fundamental function in Umbraco and we have a lot of customer pages, which use this function.


Ian 11 Jul 2013, 12:23:07

Potential Workround:

I remember there was once a bug in Version 4.x where the dates were squiffy - the solution was to set the SQL User language to "British English" - so that's what I do now when setting up sites.

However - I have managed to get round the above situation by changing the SQL User's account to "English" rather than "British English" I don't know whether this has introduced any other bugs elsewhere though, but the remove at now works as expected - it's still a Bug though!

Ian


Shannon Deminick 17 Jul 2013, 02:18:45

The issue is in these classes: PocoToSqlExpressionHelper and ModelToSqlExpressionHelper

These classes create a string date format based on an 'invariant culture' but this will essentially still format based on the current culture specified for the thread. So if this culture is the same as the one specified in your database you're probably ok, but since different domains in Umbraco have different cultures then this will not work as expected.

So I've fixed up these classes and created a new ToIsoString DateTime extension to make sure the dates for SQL are always formatted as yyyy-MM-dd hh:mm:ss which is globally recognizable.


Shannon Deminick 17 Jul 2013, 04:15:15

sorry that was meant to say yyyy-MM-dd HH:mm:ss


Shannon Deminick 17 Jul 2013, 04:16:56

Fixed in 1c70570078d83b58d31bbb23646e2fa51842b69a, @Seb if you can review that'd be awesome.


Sebastiaan Janssen 18 Jul 2013, 10:13:15

Looks good, tested in MySQL as well and it all works.


Priority: Critical

Type: Bug

State: Fixed

Assignee:

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions: 6.0.0, 6.0.1, 6.0.2, 6.0.3, 6.0.4, 6.1.1, 6.0.6, 6.0.5, 6.0.7, 6.1.2

Due in version: 6.1.3

Sprint:

Story Points:

Cycle: