‎02-23-2019 02:32 AM
‎02-26-2019 04:08 PM
Convert(Date,Substring(SubQuery2.Value, 1, 8)) As [Deploy Date 2]
Substring(SubQuery2.Value, 1, 4) + '-' + Substring(SubQuery2.Value, 5, 2) + '-' + Substring(SubQuery2.Value, 7, 2)
‎03-14-2019 06:28 AM
‎03-01-2019 07:36 PM
‎02-25-2019 10:35 PM
Convert(Date,Left(SubQuery2.Value, 8)) As [Deploy Date 2]
‎02-25-2019 11:50 PM
Convert(Date,Left(SubQuery2.Value, 8)) As [Deploy Date 2],
Convert(DateTime,Left(SubQuery2.Value, 8)) As [Deploy Date 2],
Select Top 1000000 tblAssets.AssetName As Computer,
tblAssets.Username,
tblOperatingsystem.Caption As OS,
tblAssets.OScode As [OS Build],
tblAssets.Version As [OS Version],
tblAssetCustom.Manufacturer As Make,
tblAssetCustom.Model As Model,
tblAssetCustom.Serialnumber As SN,
tblWinSAT.WinSPRLevel As WinSAT,
Cast(tblAssets.Memory / 1024 As numeric) As [RAM GB],
tblAssets.Processor As CPU,
Cast(Cast(tblFloppy.Size As bigint) / Power(10, 9) As numeric) As [Drive GB],
Case
When tblEncryptableVolume.ProtectionStatus = 0 Then ''
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
Else 'UNKNOWN'
End As BitLocker,
tblFloppy.Model As [Drive Model],
tblFloppy.FirmwareRevision As [Drive FW],
tblFloppy.SerialNumber As [Drive SN],
SubQuery2.Value As [Deploy Date],
Convert(Date,Left(SubQuery2.Value, 8)) As [Deploy Date 2],
tblOperatingsystem.InstallDate As Install,
tblAssetCustom.PurchaseDate As Purchase,
tblAssetCustom.Warrantydate As Warranty,
tblAssets.LastActiveScan As Active,
tblAssets.Lastseen As Last,
tblAssets.Lasttried As Tried,
tblAssets.Firstseen As Initial,
SubQuery1.Value As [Deploy Name]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Left Join (Select tblRegistry.AssetID,
tblRegistry.Value
From tblRegistry
Where tblRegistry.Valuename = 'Task Sequence Name') SubQuery1 On
SubQuery1.AssetID = tblAssets.AssetID
Left Join (Select tblRegistry.AssetID,
tblRegistry.Value
From tblRegistry
Where tblRegistry.Valuename = 'Deployment Timestamp') SubQuery2 On
SubQuery2.AssetID = tblAssets.AssetID
Inner Join tblEncryptableVolume On
tblAssets.AssetID = tblEncryptableVolume.AssetId
Inner Join tblWinSAT On tblAssets.AssetID = tblWinSAT.AssetId
Where tblOperatingsystem.Caption Not Like '%Windows Server%' And
tblAssetCustom.Model <> 'Virtual Machine' And
tblFloppy.Name Like '%PHYSICALDRIVE0' And tblDiskdrives.Caption = 'C:' And
tblAssetCustom.State = 1 And tblEncryptableVolume.DriveLetter = 'C:'
‎02-25-2019 05:39 PM
CONVERT(Date, Left('20181025162152.000000-420', 8))seems to work. Substitute your data field for the literal value.
CONVERT(DateTime, Left('2018-10-25T16:21:52.000000-420', 19))works.
‎02-25-2019 08:36 PM
RC62N wrote:
If all you want is the date and you're certain of the format,CONVERT(Date, Left('20181025162152.000000-420', 8))seems to work. Substitute your data field for the literal value.
I admit I was a little surprised that that worked. I assumed that dashes would be needed to separate the YYYY-MM-DD values, but apparently that's not the case. From some quick Googling, YYYYMMDD is an acceptable date format for ISO 8601. If you want the time and timezone offset, though, that will take some slicing & dicing using standard string functions. If you insert the required elements, for example,CONVERT(DateTime, Left('2018-10-25T16:21:52.000000-420', 19))works.
‎02-25-2019 08:51 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now