MSSQL: Convert YYYYMMSSTHHMMSSZ to Datetime. - Aria-Akbariyeh/Docs GitHub Wiki

Below is part of an answer to the case #CS0196805. A good example of how to convert YYYYMMSSTHHMMSSZ to Datetime in T-SQL:

Running the below SELECT query against your master database pulls all media items with the publishing restriction where:

  1. Item is from from Versioned and Unversioned JPEG Media Item.
  2. Publishing restriction is set for the item.
  3. Publish Restriction's end date is set to some time before current time
SELECT *
FROM [database_master].[dbo].[versionedFields]
WHERE FieldId = '{4C346442-E859-4EFD-89B2-44AEDF467D21}'
And Value <> ''
And CONVERT(DATETIME,STUFF(STUFF(STUFF(Replace(Replace(Value,'T',''),'Z',''),13,0,':'),11,0,':'),9,0,' ')) < CURRENT_TIMESTAMP
And ItemID IN (SELECT ID
FROM [database_master].[dbo].[Items]
WHERE TemplateID = '{DAF085E8-602E-43A6-8299-038FF171349F}'
OR TemplateID = '{EB3FB96C-D56B-4AC9-97F8-F07B24BB9BF7}'