Help reporting a funky timestamp from registry

[STRiCT]

I am reporting on a registry value that produces a time stamp that looks like '20181025162152.000000-420'. I simply want to convert this to a short date that Excel can read. Using the example time stamp above it should look like '10/25/2018'.

I was thinking CASE could truncate and convert, but I am not very good at SQL syntax.

Any help would be greatly appreciated!

Here is the report I am working on:

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,
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],
tblFloppy.Model As [Drive Model],
tblFloppy.FirmwareRevision As [Drive FW],
tblFloppy.SerialNumber As [Drive SN],
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],
SubQuery2.Value As [Deploy Date]
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
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

[RCorbeil]

Are you, by any chance, using SQL Server Compact Edition? According to this page, it doesn't appear to provide the Left() and Right() functions, among others. If so, try using Substring() instead.

Convert(Date,Substring(SubQuery2.Value, 1, 8)) As [Deploy Date 2]

If you are using SQL Compact and if it also has issues converting to a date, you could try just formatting the string instead. You say that you're transferring the results to Excel, so it will probably recognize the format and convert it itself.

Substring(SubQuery2.Value, 1, 4) + '-' + Substring(SubQuery2.Value, 5, 2) + '-' + Substring(SubQuery2.Value, 7, 2)

[STRiCT]

Might converting my db to SQL Express alleviate this problem?

[STRiCT]

That is exactly the case! I really appreciate your help.

[RCorbeil]

As you've formatted it, you're trying to convert the first eight characters of the text 'SubQuery2.Value' to a date. Drop the quote marks so that you're referencing the value stored in SubQuery2.Value.

Convert(Date,Left(SubQuery2.Value, 8)) As [Deploy Date 2]

What you're doing:
- Take the value of the field SubQuery2.Value: '20181025162152.000000-420'
- Trim that down to just the first eight characters: '20181025' (Good news! That's a recognized form of ISO 8601 date format)
- Convert the eight characters of text to a date

If your data contains values other than the YYYYMMDDhhmmss.dddddd-zzz that you've provided as an example, you'll want to wrap the Convert() in a CASE statement where you check that the value is reasonable before trying to convert it to a date.

[STRiCT]

Thanks again for your reply! I am not sure why this isnt working for me.

When I try:

Convert(Date,Left(SubQuery2.Value, 8)) As [Deploy Date 2],

I get the error: The specified data type is not valid. [ Data type (if known) = Date ]

When I try:

Convert(DateTime,Left(SubQuery2.Value, 8)) As [Deploy Date 2],

I get the error: There was an error parsing the query. [ Token line number = 1,Token line offset = 833,Token in error = Left ]

This is the full report code:

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:'

[RCorbeil]

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.

[STRiCT]

I apologize for my ignorance. I tried:

Convert(Date,Left('SubQuery2.Value', 8)) As [Deploy Date 2],

And received the error 'The specified data type is not valid. [ Data type (if known) = Date ]'

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.

[STRiCT]

Also tried:

Convert(DateTime,Left('SubQuery2.Value', 8)) As [Deploy Date 2],

and got the error: [ Token line number = 1,Token line offset = 632,Token in error = Left ]