CODEWITHSUNDEEP
sql-serverdynamic-sql
user1331734
user1331734

Reputation: 87

Using CONVERT with a data table column in dynamic SQL

I have the following dynamic query to check if today's date is between the FTOStartDate and FTOEndDDate from a data table.

I am getting the following errors:

Msg 207, Level 16, State 1, Line 18
Invalid column name 'FTOStartDate'.
Msg 207, Level 16, State 1, Line 19
Invalid column name 'FTOEndDate'.

All the examples I looked at used a variable in the convert function; I need to use a column name from the data table.

Thanks, Brad

SET @env = dbo.GetSQLEnvironment();
SET @assigned_claim_table = 'grp_clm_ops_d' + @env + '.dbo.cauDCMFToAssign';

SET @sql = 'SELECT DCMNumber,  FTOStartDate ' + 
           'FROM ' + @assigned_claim_table + ' ' +
           'WHERE '  + CONVERT(VARCHAR(10), GETDATE(), 110) + 
           ' NOT BETWEEN ' + CONVERT(VARCHAR(10), FTOStartDate, 110) + 
           'AND ' +  CONVERT(VARCHAR(10), FTOEndDate, 110) + ' '

EXEC sp_executesql @sql;

Upvotes: 1

Views: 1834

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

I realize you've already resolved the immediate issue, but I just wanted to add that if the columns in the table are of any date/time type, all of these conversions are absolutely unnecessary. You can just as easily do:

SET @sql = 'SELECT DCMNumber,  FTOStartDate FROM ' 
  + @assigned_claim_table 
  + ' WHERE GETDATE() NOT BETWEEN FTOStartDate AND FTOEndDate;';

If they're not date/time, then:

SET @sql = 'SELECT DCMNumber,  FTOStartDate FROM ' 
  + @assigned_claim_table 
  + ' WHERE GETDATE() NOT BETWEEN CONVERT(DATETIME, FTOStartDate)
      AND CONVERT(DATETIME, FTOEndDate);';

If you're trying to strip time from the columns, safer to do this than string conversions to ambiguous formats:

SET @sql = 'SELECT DCMNumber,  FTOStartDate FROM ' 
  + @assigned_claim_table 
  + ' WHERE GETDATE() NOT BETWEEN DATEDIFF(DAY, 0, FTOStartDate)
      AND DATEDIFF(DAY, 0, FTOEndDate);';

Not that BETWEEN or NOT BETWEEN is a good idea anyway - like your 110 format, that is ambiguous and subject to misinterpretation.

Upvotes: 0

Jim
Jim

Reputation: 3510

Why not do:

SET @sql = 'SELECT DCMNumber,  FTOStartDate ' + 
           'FROM ' + @assigned_claim_table + ' ' +
           'WHERE CONVERT(VARCHAR(10), GETDATE(), 110) NOT BETWEEN ' + 
           ' CONVERT(VARCHAR(10), FTOStartDate, 110) AND CONVERT(VARCHAR(10), FTOEndDate, 110)'

SQL Server has no issue running converts inside of a dynamic query.

Upvotes: 1

smirkingman
smirkingman

Reputation: 6358

Without the underlying definitions, it would seem that the table named in @assigned_claim_table doesn't contain the columns FTOStartDate and FTOEndDate.

That said, even if the table did, the query will produce the wrong results because 110 is US format M/D/Y so the comparison wouldn't work correctly; ISO 112 would be correct.

Upvotes: 0

Related Questions