CODEWITHSUNDEEP
sql-serversql-server-2008t-sqldynamic-sql
san463
san463

Reputation: 21

dynamic sql formula string column conversion to date

The following code consists of a temp table #s in which Param2 column is a string type.

containing expressions, nulls and values in it. My code is capturing all the nulls and values of the param2 column. However it is also getting the date string as it is. I would like to replace the date string to date value dynamically. I am getting all the other values including nulls in the param2 column but instead of date string i need date value to be populated...below please find my code....

DROP TABLE #S
CREATE TABLE #S (ID VARCHAR(2),REPORTNAME VARCHAR(2),PARAM2 VARCHAR(300))

INSERT INTO #S(ID,REPORTNAME,PARAM2)
      SELECT '02','A',NULL
UNION SELECT '03','B',NULL
UNION SELECT '06','E','ALL'
UNION SELECT '07','F','ALL'
UNION SELECT '08','G','CONVERT(CHAR(10),GETDATE()-1,101)'
UNION SELECT '09','H','CONVERT(CHAR(10),GETDATE()+1,101)'
UNION SELECT '10','I','CONVERT(CHAR(10),GETDATE()+10,101)'
UNION SELECT '11','J','ALL'
UNION SELECT '12','K',NULL
UNION SELECT '13','l','PRODUCTION'
GO

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = 'SELECT ID,REPORTNAME, CASE WHEN PARAM2 NOT LIKE ''%CONVERT%'' THEN PARAM2  WHEN PARAM2 IS NULL THEN ' + QUOTENAME('NULL','''') + ' ELSE PARAM2 END AS PARAM2  FROM #S ORDER BY ID' FROM #S
PRINT @SQL 
EXEC(@SQL)

Desired Output Example:

ID REPORTNAME PARAM2
08  G          12/08/2014  --(YESTERDAYS DATE)

Current Output Example:

ID REPORTNAME PARAM2
    08  G          convert(char(10),getdate()-1,101)

Upvotes: 2

Views: 157

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

How about this one?

DECLARE @SQL VARCHAR(MAX)

SELECT @SQL =  
'SELECT 
    ID,
    REPORTNAME, 
    PARAM2 = 
        CASE
'

SELECT @SQL = @SQL + 
'           WHEN ID = ' + CONVERT(VARCHAR(10), S.ID) + ' THEN ' + S.PARAM2 + CHAR(10)
FROM(
    SELECT 
        DISTINCT ID, PARAM2
    FROM #S 
    WHERE PARAM2  LIKE '%CONVERT%'
)S

SELECT @SQL = @SQL +
'           ELSE ISNULL(PARAM2, ''NULL'')
        END
FROM #S 
ORDER BY ID'

PRINT @SQL
EXEC(@SQL)

Upvotes: 2

Related Questions