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.
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"
}
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?
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...
-- 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
Apparently Umbraco fetches all the previous versions (from cmsPropertyData) for a content item during a publish event to determine the published version.
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.
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.
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:
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'.
Lastly, I've also looked at the values for the properties in the database.
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:
Any help would be greatly appreciated.
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?
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
Apparently Umbraco fetches all the previous versions (from cmsPropertyData) for a content item during a publish event to determine the published version.
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.
May i ask where you set that?
What do you mean exactly? Where I set the cmsPropertyData to NULL?
Yes because i have the same problem in 10 after i imported my umbraco 7 website.
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.
is working on a reply...