1

I am writing an application responsible for archiving data and we have the configuration in a database table

Id    |    TableName    |    ColumnName    |    RetentionAmountInDays
1     |    DeviceData   |    MetricTime    |    3

So when faced with this configuration, I should archive all data in the DeviceData table where the MetricTime value is before 3 days ago.

The reason I am doing this dynamically is the table names and column names differ (there would be multiple rows)

For each configuration this stored procedure is called

CREATE PROCEDURE GetDynamicDataForArchive
    @TableName nvarchar(100),
    @ColumnName nvarchar(100),
    @OlderThanDate datetime2(7)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql nvarchar(1000);
    SET @sql = 'SELECT * FROM ' + @TableName + ' WHERE ' + @ColumnName + ' < @OlderThanDate';
    exec sp_executesql @sql;
END

And an example exec line

exec dbo.GetDynamicDataForArchive 'DeviceData', 'MetricTime', '2017-04-16 20:29:29.647'

This results in:

Conversion failed when converting date and/or time from character string.

So something is up with how I am passing in the datetime2 or how I am forming the where clause.

andrewb
  • 2,995
  • 7
  • 54
  • 95

1 Answers1

2

Replace this statement:

SET @sql = 'SELECT * FROM ' + @TableName + ' WHERE ' + @ColumnName + ' < @OlderThanDate'

by

SET @sql = 'SELECT * FROM ' + @TableName + ' WHERE [' + @ColumnName + '] < ''' +  cast(@OlderThanDate as varchar(23)) + '''';

I don't particularly like having to convert the datetime to a varchar value though, perhaps there is a better way to do this(?).

Giorgos Altanis
  • 2,742
  • 1
  • 13
  • 14