Relog.exe imports a CounterDateTime data incorrectly (different solution suggested)

[JendaPerl]

Importing the date as a char(24) is ... suboptimal. It should have been a datetime2(3), but unless the relog itself gets updated, this is how the tool imports the data.
The tool doesn't care though whether it imports into a table or a view so it's possible to

1. add a persisted computed column that converts the date to datetime2(3)
2. rename the table
3. create a view named CounterData as SELECT FROM TheRenamedTable

and then RELOG will happily import the data and you can add indexes on the datetime2 column and treat it as a proper date.

---

ALTER TABLE CounterData ADD RecordedAt AS CONVERT(datetime2(3), convert(varchar(23), CounterDateTime), 121) PERSISTED;
go

EXEC sp_rename 'dbo.CounterData', 'CounterDataConverted';
go

CREATE VIEW dbo.CounterData
AS
SELECT [GUID], [CounterID], [RecordIndex], [CounterDateTime], [CounterValue], [FirstValueA], [FirstValueB], [SecondValueA], [SecondValueB], [MultiCount]
FROM [dbo].[CounterDataConverted]
go

It would probably be best to create another view that would not contain the char(24) column at all and suggest that people use that view and not the table.

(Tested on Win 11 Pro and SQL Server 2022)

[JendaPerl]

Another option is to rename the CounterDateTime column in the table to (say) CounterDateTime_raw and name the computed column CounterDateTime and of course include CounterDateTime_raw as CounterDateTime in the view:

`EXEC sp_rename 'dbo.CounterData.CounterDateTime', 'CounterDateTime_raw';
go

ALTER TABLE CounterData ADD CounterDateTime AS CONVERT(datetime2(3), convert(varchar(23), CounterDateTime_raw), 121) PERSISTED;
go

EXEC sp_rename 'dbo.CounterData', 'CounterDataToUse';
go

CREATE VIEW dbo.CounterData
AS
SELECT [GUID], [CounterID], [RecordIndex], [CounterDateTime_raw] as [CounterDateTime], [CounterValue], [FirstValueA], [FirstValueB], [SecondValueA], [SecondValueB], [MultiCount]
FROM dbo.CounterDataToUse
go

CREATE NONCLUSTERED INDEX IX_CounterDataConverted_RecordedAt_CounterID ON dbo.CounterDataToUse
(
CounterDateTime,
CounterID
)
INCLUDE (CounterValue)
WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
`

This way it's likely not necessary to add another view, you just need to use (and point people to) the renamed table and warn from using the view.

[PiJoCoder]

@JendaPerl thanks for your contribution to SQL Nexus quality and offering your suggestions to our consideration.