Notification

Icon
Error

Help reporting a funky timestamp from registry

Posted: Saturday, February 23, 2019 2:32:17 AM(UTC)
STRiCT

STRiCT

Member Original PosterPosts: 6
0
Like
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
RC62N
#1RC62N Member Posts: 337  
posted: 2/25/2019 5:39:45 PM(UTC)
If all you want is the date and you're certain of the format,
Code:
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,
Code:
CONVERT(DateTime, Left('2018-10-25T16:21:52.000000-420', 19))
works.
STRiCT
#2STRiCT Member Original PosterPosts: 6  
posted: 2/25/2019 8:36:56 PM(UTC)
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 ]'

Originally Posted by: RC62N Go to Quoted Post
If all you want is the date and you're certain of the format,
Code:
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,
Code:
CONVERT(DateTime, Left('2018-10-25T16:21:52.000000-420', 19))
works.


STRiCT
#3STRiCT Member Original PosterPosts: 6  
posted: 2/25/2019 8:51:09 PM(UTC)
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 ]

RC62N
#4RC62N Member Posts: 337  
posted: 2/25/2019 10:35:49 PM(UTC)
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.
Code:
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
#5STRiCT Member Original PosterPosts: 6  
posted: 2/25/2019 11:50:10 PM(UTC)
Thanks again for your reply! I am not sure why this isnt working for me.

When I try:
Code:
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:
Code:
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:
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:'
RC62N
#6RC62N Member Posts: 337  
posted: 2/26/2019 4:08:48 PM(UTC)
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.
Code:
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.
Code:
Substring(SubQuery2.Value, 1, 4) + '-' + Substring(SubQuery2.Value, 5, 2) + '-' + Substring(SubQuery2.Value, 7, 2)
STRiCT
#7STRiCT Member Original PosterPosts: 6  
posted: 3/1/2019 7:36:57 PM(UTC)
That is exactly the case! I really appreciate your help.
STRiCT
#8STRiCT Member Original PosterPosts: 6  
posted: 3/14/2019 6:28:26 AM(UTC)
Might converting my db to SQL Express alleviate this problem?

Active Discussions

Lansweeper report of workstation with 2nd screen
by  Esben.D   Go to last post Go to first unread
Last post: 7/12/2019 12:43:16 PM(UTC)
Lansweeper Lansweeper warnings sent to an email
by  MilicaM  
Go to last post Go to first unread
Last post: 7/12/2019 10:36:44 AM(UTC)
Lansweeper Number of users in a group
by  matt steele   Go to last post Go to first unread
Last post: 7/12/2019 10:34:17 AM(UTC)
Lansweeper Report of assets names behind switches/routers
by  dsimonspan  
Go to last post Go to first unread
Last post: 7/11/2019 10:41:17 AM(UTC)
Lansweeper Intel SSD and Processor Diagnostic Tool Vulnerability
by  Esben.D   Go to last post Go to first unread
Last post: 7/11/2019 8:24:34 AM(UTC)
Lansweeper Patch Tuesday report, last 3 months
by  Viper  
Go to last post Go to first unread
Last post: 7/11/2019 2:32:20 AM(UTC)
Lansweeper Adobe Security Update July
by  Esben.D   Go to last post Go to first unread
Last post: 7/10/2019 3:45:16 PM(UTC)
Lansweeper Firefox 68 Security Update
by  Esben.D  
Go to last post Go to first unread
Last post: 7/10/2019 3:35:17 PM(UTC)