systemstring could not be converted to type systemdatetime after upgrading from 7515 to 7157 - Using Umbraco and getting started

Arjan H. 226 posts 463 karma points c-trib

Nov 01, 2021 @ 15:55

System.String" could not be converted to type "System.DateTime" after upgrading from 7.5.15 to 7.15.7

I'm getting the following error when publishing or unpublishing an existing content item that contains a single Date Picker data type:

An error occured

Value "1972" of type "System.String" could not be converted to type "System.DateTime" which is expected by property type "date".

Exception Details

System.InvalidOperationException: Value "1972" of type "System.String" could not be converted to type "System.DateTime" which is expected by property type "date".

Part of the stack trace:

at Umbraco.Core.Models.Property.ThrowTypeException(Object value, Type expected, String alias)

   at Umbraco.Core.Models.Property.set_Value(Object value)

   at Umbraco.Core.Persistence.Factories.PropertyFactory.BuildEntity(IReadOnlyCollection`1 dtos, PropertyType[] compositionTypeProperties, DateTime createDate, DateTime updateDate)

   at Umbraco.Core.Persistence.Repositories.VersionableRepositoryBase`2.GetPropertyCollection(PagingSqlQuery pagingSqlQuery, IReadOnlyCollection`1 documentDefs)

   at Umbraco.Core.Persistence.Repositories.ContentRepository.ProcessQuery(Sql sqlFull, PagingSqlQuery pagingSqlQuery, Boolean withCache, Boolean includeAllVersions)

   at Umbraco.Core.Persistence.Repositories.ContentRepository.GetAllVersions(Int32 id)

   at Umbraco.Core.Services.ContentService.GetVersions(Int32 id)

   at Umbraco.Core.Services.ContentService.GetPublishedVersion(Int32 id)

   at Umbraco.Web.Routing.RedirectTrackingEventHandler.ContentService_Publishing(IPublishingStrategy sender, PublishEventArgs`1 args)

   at Umbraco.Core.Events.ScopeEventDispatcherBase.DispatchCancelable[TSender,TArgs](TypedEventHandler`2 eventHandler, TSender sender, TArgs args, String eventName)

   at Umbraco.Core.Publishing.PublishingStrategy.Umbraco.Core.Publishing.IPublishingStrategy2.Publish(IScopeUnitOfWork uow, IContent content, Int32 userId)

   at Umbraco.Core.Services.ContentService.SaveAndPublishDo(IContent content, Int32 userId, Boolean raiseEvents)

   at Umbraco.Core.Services.ContentService.Umbraco.Core.Services.IContentServiceOperations.SaveAndPublish(IContent content, Int32 userId, Boolean raiseEvents)

   at Umbraco.Core.Services.ContentService.SaveAndPublishWithStatus(IContent content, Int32 userId, Boolean raiseEvents)

   at Umbraco.Web.Editors.ContentController.PostSaveInternal(ContentItemSave contentItem, Func`2 saveMethod)

   at Umbraco.Web.Editors.ContentController.PostSave(ContentItemSave contentItem)

[...]

I don't see this error on Saving, only on Save & Publish or Unpublish, and only for conent items that had a date set for this Date Picker property before the upgrade. Adding new content items with a date, or adding a date to existing content items that didn't have a date set before the upgrade is not a problem.

But the weirdest thing is that I've checked the data that's being posted to the server for the error I mentioned above. And the posted data does NOT contain "1972" as one of the property values. Please note that only the 'date' property is a Date Picker, the 'dateManual' property is a 'Textstring'.

{
  "id" : 1206,
  "properties" : [
    {
      "id" : 345914,
      "alias" : "listingImage",
      "value" : "1357"
    },
    {
      "id" : 345911,
      "alias" : "name",
      "value" : "AC/DC"
    },
    {
      "id" : 345924,
      "alias" : "productCode",
      "value" : "ACDC001DC"
    },
    {
      "id" : 345918,
      "alias" : "pricingGroup",
      "value" : "1338"
    },
    {
      "id" : 345909,
      "alias" : "sortingWeight",
      "value" : "100"
    },
    {
      "id" : 345907,
      "alias" : "flash",
      "value" : null
    },
    {
      "id" : 345916,
      "alias" : "artists",
      "value" : "1146"
    },
    {
      "id" : 345921,
      "alias" : "alsoKnownAs",
      "value" : ""
    },
    {
      "id" : 345910,
      "alias" : "photographer",
      "value" : "1211"
    },
    {
      "id" : 345920,
      "alias" : "location",
      "value" : "Hammersmith Odeon, London, UK"
    },
    {
      "id" : 345917,
      "alias" : "date",
      "value" : "1979-11-01"
    },
    {
      "id" : 345926,
      "alias" : "dateManual",
      "value" : "November"
    },
    {
      "id" : 345922,
      "alias" : "musicEra",
      "value" : "1329"
    },
    {
      "id" : 345919,
      "alias" : "edition",
      "value" : "2019"
    },
    {
      "id" : 345912,
      "alias" : "printType",
      "value" : "2267"
    },
    {
      "id" : 345925,
      "alias" : "paperType",
      "value" : "2284"
    },
    {
      "id" : 345908,
      "alias" : "printQuality",
      "value" : "Archival Digital Fine-Art print on Hahnemuhle paper"
    },
    {
      "id" : 345915,
      "alias" : "shortDescription",
      "value" : "AC/DC's guitarist Angus Young is a great musician but very difficult to photograph as he moves non-stop. - Hammersmith Odeon, London, UK. "
    },
    {
      "id" : 345913,
      "alias" : "tags",
      "value" : [ "heavy metal" ]
    },
    {
      "id" : 345923,
      "alias" : "description",
      "value" : "<p>Angus Young, with AC/DC performing at the Hammersmith Odeon, London n November 1979. Photographer David Corio recalls, \"This was the first hard rock concert I had ever been to. It was quite a shock as lead singer Bon Scott's voice was piercing and the whole band played at such a high volume it was almost unbearable. Within 3 months Scott was to die after a heavy bout of drinking.\"</p>\n<p>\"When taking photos from the side of the stage I will often find my head is alongside the huge PA speakers and you have to grit your teeth very hard to stop your eardrums vibrating. Ear-plugs have very little effect as the decibel level is so high. I saw one fan in the audience with blood pouring from one of his ears - his ear-drum had been ruptured by the volume! I've seen this happen one other time at a Ted Nugent show at the same venue.\"</p>\n<p>\"AC/DC's guitarist Angus Young is a great musician but very difficult to photograph as he moves non-stop all over the stage for the entirety of the show. He still plays the Gibson SG guitar and wears the distinctive school blazer, shorts and satchel even though he is now well over 50 years old!\"<em> <br /></em></p>"
    },
    {
      "id" : 345906,
      "alias" : "useManualPricing",
      "value" : "0"
    },
    {
      "id" : 345905,
      "alias" : "manualPricing",
      "value" : {
        "fieldsets" : []
      }
    },
    {
      "id" : 345897,
      "alias" : "metaData",
      "value" : {
        "title" : "AC/DC | Hammersmith Odeon Print | David Corio Photo",
        "description" : "David Corio print of guitarist Angus Young from AC/DC taken at the Hammersmith Odeon, London, 1979. Limited edition photos, images &  prints for sale.",
        "urlName" : "",
        "noIndex" : false
      }
    },
    {
      "id" : 345896,
      "alias" : "canonicalLink",
      "value" : ""
    },
    {
      "id" : 345903,
      "alias" : "pageMenuTitle",
      "value" : ""
    },
    {
      "id" : 345904,
      "alias" : "umbracoRedirect",
      "value" : ""
    },
    {
      "id" : 345898,
      "alias" : "umbracoUrlAlias",
      "value" : ""
    },
    {
      "id" : 345902,
      "alias" : "umbracoNaviHide",
      "value" : "0"
    },
    {
      "id" : 345901,
      "alias" : "siteSearchHide",
      "value" : "0"
    },
    {
      "id" : 345900,
      "alias" : "siteSearchSnippet",
      "value" : ""
    },
    {
      "id" : 345899,
      "alias" : "googleAnalyticsCode",
      "value" : ""
    }
  ],
  "name" : "AC/DC (ACDC001DC)",
  "contentTypeAlias" : "print",
  "parentId" : 1559,
  "action" : "publish",
  "expireDate" : null,
  "releaseDate" : null,
  "templateAlias" : "Print"
}

Lastly, I've also looked at the values for the properties in the database.

enter image description here

You'll notice that only id 357764 contains a date value. And again, non of the values contain '1972', so where on earth is this value coming from? And why do I get this conversion exception only when saving content items that contained a (valid) date before the upgrade, and not for new content items? I've also tried clearing the Date Picker property, but I'm still getting the same error.

On another content item I'm getting the following error:

Value "1st November 1979" of type "System.String" could not be converted to type "System.DateTime" which is expected by property type "date".

This one makes a little more sense, because the 'date' property actually contains the value '1979-11-01'. But I still don't understand where or how this is being translated to '1st November 1979' before publishing the content and resulting in the error. The post data for this content item looks like this:

{
  "id" : 1206,
  "properties" : [
    {
      "id" : 376278,
      "alias" : "listingImage",
      "value" : "1357"
    },
    {
      "id" : 376275,
      "alias" : "name",
      "value" : "AC/DC"
    },
    {
      "id" : 376288,
      "alias" : "productCode",
      "value" : "ACDC001DC"
    },
    {
      "id" : 376282,
      "alias" : "pricingGroup",
      "value" : "1338"
    },
    {
      "id" : 376273,
      "alias" : "sortingWeight",
      "value" : 100
    },
    {
      "id" : 376271,
      "alias" : "flash",
      "value" : null
    },
    {
      "id" : 376280,
      "alias" : "artists",
      "value" : "1146"
    },
    {
      "id" : 376285,
      "alias" : "alsoKnownAs",
      "value" : ""
    },
    {
      "id" : 376274,
      "alias" : "photographer",
      "value" : "1211"
    },
    {
      "id" : 376284,
      "alias" : "location",
      "value" : "Hammersmith Odeon, London, UK"
    },
    {
      "id" : 376281,
      "alias" : "date",
      "value" : "1979-11-01"
    },
    {
      "id" : 376290,
      "alias" : "dateManual",
      "value" : "November"
    },
    {
      "id" : 376286,
      "alias" : "musicEra",
      "value" : "1329"
    },
    {
      "id" : 376283,
      "alias" : "edition",
      "value" : "2019"
    },
    {
      "id" : 376276,
      "alias" : "printType",
      "value" : "2267"
    },
    {
      "id" : 376289,
      "alias" : "paperType",
      "value" : "2284"
    },
    {
      "id" : 376272,
      "alias" : "printQuality",
      "value" : "Archival Digital Fine-Art print on Hahnemuhle paper"
    },
    {
      "id" : 376279,
      "alias" : "shortDescription",
      "value" : "AC/DC's guitarist Angus Young is a great musician but very difficult to photograph as he moves non-stop. - Hammersmith Odeon, London, UK. "
    },
    {
      "id" : 376277,
      "alias" : "tags",
      "value" : [ "heavy metal" ]
    },
    {
      "id" : 376287,
      "alias" : "description",
      "value" : "<p>Angus Young, with AC/DC performing at the Hammersmith Odeon, London n November 1979. Photographer David Corio recalls, \"This was the first hard rock concert I had ever been to. It was quite a shock as lead singer Bon Scott's voice was piercing and the whole band played at such a high volume it was almost unbearable. Within 3 months Scott was to die after a heavy bout of drinking.\"</p>\n<p>\"When taking photos from the side of the stage I will often find my head is alongside the huge PA speakers and you have to grit your teeth very hard to stop your eardrums vibrating. Ear-plugs have very little effect as the decibel level is so high. I saw one fan in the audience with blood pouring from one of his ears - his ear-drum had been ruptured by the volume! I've seen this happen one other time at a Ted Nugent show at the same venue.\"</p>\n<p>\"AC/DC's guitarist Angus Young is a great musician but very difficult to photograph as he moves non-stop all over the stage for the entirety of the show. He still plays the Gibson SG guitar and wears the distinctive school blazer, shorts and satchel even though he is now well over 50 years old!\"<em> <br /></em></p>"
    },
    {
      "id" : 376270,
      "alias" : "useManualPricing",
      "value" : "0"
    },
    {
      "id" : 376269,
      "alias" : "manualPricing",
      "value" : {
        "fieldsets" : []
      }
    },
    {
      "id" : 376261,
      "alias" : "metaData",
      "value" : {
        "title" : "AC/DC | Hammersmith Odeon Print | David Corio Photo",
        "description" : "David Corio print of guitarist Angus Young from AC/DC taken at the Hammersmith Odeon, London, 1979. Limited edition photos, images &  prints for sale.",
        "urlName" : "",
        "noIndex" : false
      }
    },
    {
      "id" : 376260,
      "alias" : "canonicalLink",
      "value" : ""
    },
    {
      "id" : 376267,
      "alias" : "pageMenuTitle",
      "value" : ""
    },
    {
      "id" : 376268,
      "alias" : "umbracoRedirect",
      "value" : ""
    },
    {
      "id" : 376262,
      "alias" : "umbracoUrlAlias",
      "value" : ""
    },
    {
      "id" : 376266,
      "alias" : "umbracoNaviHide",
      "value" : "0"
    },
    {
      "id" : 376265,
      "alias" : "siteSearchHide",
      "value" : "0"
    },
    {
      "id" : 376264,
      "alias" : "siteSearchSnippet",
      "value" : ""
    },
    {
      "id" : 376263,
      "alias" : "googleAnalyticsCode",
      "value" : ""
    }
  ],
  "name" : "AC/DC (ACDC001DC)",
  "contentTypeAlias" : "print",
  "parentId" : 1559,
  "action" : "publish",
  "expireDate" : null,
  "releaseDate" : null,
  "templateAlias" : "Print"
}

Any help would be greatly appreciated.

Copy Link

Arjan H. 226 posts 463 karma points c-trib

Nov 01, 2021 @ 16:10

I did some more testing. It might not be related to the Date Picker property after all. Some of the content items (with or without a Date Picker value set before the upgrade to 7.15.7) can be published or unpublished without a problem, but some result in the above error. I'm having a hard time finding out where the data that's causing the System.String to System.DateTime conversion errors is coming from though. So any pointers?

Copy Link

Arjan H. 226 posts 463 karma points c-trib

Nov 01, 2021 @ 16:32

It appears older versions of the content were causing the issue. I've used the SQL statement below to clean up older content versions (kept the last 10 versions). This immediately resolved the (un)publish issues. I have no idea why the older content versions would interfere when publishing or unpublish a content item though...

https://gist.github.com/enkelmedia/b322025eab775344e6c3762a776590a1

-- set how many versions to keep using @numberOfVersionToKeep, this can be set to anything from 0 and above. 0 will clean all versions except the current of course.
-- actually delete stuff by modifying last line to 'commit tran'

begin tran
go

DECLARE @numberOfVersionToKeep int = 20

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP Table #tmp
create table #tmp (versionid uniqueidentifier)

insert #tmp select versionid from (
    select
        umbracoNode.id, umbracoNode.path,
        row_number() over ( partition by umbracoNode.id order by cmsDocument.updateDate desc) rn,
        cmsDocument.*
    from
        umbracoNode
        inner join cmsDocument on cmsDocument.nodeId = umbracoNode.id
    where
        nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972'
) t
where
    rn > @numberOfVersionToKeep
    and
    published = 0
    and
    newest = 0


DELETE FROM cmsPreviewXml WHERE versionId IN (select #tmp.VersionId from #tmp)
DELETE FROM cmsContentVersion WHERE VersionId IN (select #tmp.VersionId from #tmp)
DELETE FROM cmsPropertyData WHERE VersionId IN (select #tmp.VersionId from #tmp)
DELETE FROM cmsDocument WHERE VersionId IN (select #tmp.VersionId from #tmp)

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP Table #tmp

go
rollback tran
--commit tran

Copy Link

Arjan H. 226 posts 463 karma points c-trib

Nov 01, 2021 @ 18:33

Apparently Umbraco fetches all the previous versions (from cmsPropertyData) for a content item during a publish event to determine the published version.

enter image description here

And because the 'date' property was a 'Textstring' during the early stages of development and was later converted to a 'Date Picker' some of the oldest versioned data contained some invalid strings for this specific property, strings that couldn't be convert to dates. And this resulted in the error I mentioned in my initial post. I've set all the erroneous entries for this property in cmsPropertyData to NULL and this fixed the issues. So in the end I didn't have to delete the older versions of the data at all.

So in the end a valuable lesson to be learned: when converting a property's data type from string to date, or int (or vice versa) make sure to clean up older data versions for this property in cmsPropertyData.

Copy Link

Nick 13 posts 97 karma points c-trib

Jun 27, 2023 @ 08:16

May i ask where you set that?

Copy Link

Arjan H. 226 posts 463 karma points c-trib

Jun 27, 2023 @ 08:21

What do you mean exactly? Where I set the cmsPropertyData to NULL?

Copy Link

Nick 13 posts 97 karma points c-trib

Jun 27, 2023 @ 08:21

Yes because i have the same problem in 10 after i imported my umbraco 7 website.

Copy Link

Arjan H. 226 posts 463 karma points c-trib

Jun 27, 2023 @ 08:28

I believe the property data is now stored in the umbracoPropertyData table. So you'll have to set the old values for the relevant propertyTypeId to NULL.

Copy Link

is working on a reply...

Flag this post as spam?

System.String" could not be converted to type "System.DateTime" after upgrading from 7.5.15 to 7.15.7