RIAS
asked on
Convert column to int
Hello,
Is there a way i can convert column1 to int
to get query like :
Cheers
Is there a way i can convert column1 to int
SELECT
CASE
WHEN CAST(GETDATE() AS DATE) = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) THEN 1
ELSE
MAX ([' + @ColumnName1 + ']' + ' ) END Output'
+ ' FROM [dbo].[' + @tableName + ']'
to get query like :
select max (cast(ref as int)) as ref from table1 order by ref
Cheers
ASKER
Hello Vitor,
This a stored procedure ,your query generated syntax error .
The stored procedure is as bellow
This a stored procedure ,your query generated syntax error .
The stored procedure is as bellow
DECLARE @SQL VARCHAR(1000) = null
SET @SQL = '
SELECT
CASE
WHEN CAST(GETDATE() AS DATE) = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) THEN 1
ELSE
MAX ( [' + @ColumnName1 + ']' + ' ) END Output'
+ ' FROM [dbo].[' + @tableName + ']'
EXECUTE(@SQL)
ASKER
The problem with the original Sp query is that it does not give correct max (ref) ,need to vconvert it to integer.
Cheers
Cheers
This a stored procedure ,your query generated syntax error .Can you post the error?
ASKER
DECLARE @SQL VARCHAR(1000) = null
SET @SQL = '
SELECT
CASE
WHEN CAST(GETDATE() AS DATE) = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) THEN 1
ELSE
MAX (CAST[' + @ColumnName1 + '] AS INT)' + ' ) END Output'
+ ' FROM [dbo].[' + @tableName + ']'
EXECUTE(@SQL)
ASKER
Error :
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'Ref'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'Output'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'Ref'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'Output'.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Looks like you had an extra parentheses closing:
SET @SQL = 'SELECT CASE
WHEN CAST(GETDATE() AS DATE) = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) THEN 1
ELSE MAX (CAST[' + @ColumnName1 + '] AS INT)
END Output
FROM [dbo].[' + @tableName + ']'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cheers Vitor, thanks Pawan as well!
RIAS, why are you giving points to a solution that doesn't work?
The intention is for future people with similar issue to come here and check immediately the comments that really helped you, so working solutions.
EE's policy doesn't reward efforts (unfortunally).
The intention is for future people with similar issue to come here and check immediately the comments that really helped you, so working solutions.
EE's policy doesn't reward efforts (unfortunally).
ASKER
The solution was partially correct, but if it for fully working then will do so in future.
Cheers
Cheers
Open in new window
Btw, I'm assuming this is a dynamic SQL otherwise you can't do this.