CODEWITHSUNDEEP
sqlsql-servert-sql
neeko
neeko

Reputation: 2000

Error when running SQL stored procedure coversion failed when converting date and/or time from character string

DECLARE @i varchar(13)
DECLARE @Year varchar(30)

SET @i = 2
SET @Year = 2013

SELECT SUM(CAST([totalAfterMultiplier] as money)) as 'totalAfterMultiplier' FROM [postfinanceAfterMultiplier] 
                    WHERE CAST([ORDER] as date) >= '01/'+@i+'/'+ @Year +'' AND CAST([ORDER] as date) <= '31/'+@i+'/'+ @Year +''

I am getting this error: Conversion failed when converting date and/or time from character string.

All values in the database are varchar(50)

I have looked at the other posts and can't seem to get it working

Thank you all for your help

Upvotes: 2

Views: 1059

Answers (2)

neeko
neeko

Reputation: 2000

I found that the following solution works:

DECLARE @i varchar(13)
DECLARE @Year varchar(30)


SET @i = 2
SET @Year = 2013



SELECT SUM(CAST([totalAfterMultiplier] as money)) as 'totalAfterMultiplier' FROM [postfinanceAfterMultiplier] 
                    WHERE MONTH([ORDER]) = @i AND YEAR([ORDER]) = @Year

Upvotes: -1

roman
roman

Reputation: 117380

  1. Don't store dates as varchar.
  2. if you want to convert to date from string 'dd/mm/yyyy', use explicit format 103, like select convert(date, '01/12/2013', 103). See other formats here - CAST and CONVERT (Transact-SQL)

Upvotes: 2

Related Questions