Have spent few hours to get this working. I like to get help now. Please note MONTH AND DATES stored in single or double digits as they occur.
Unfortunately DATE TIME is stored in NVARCHAR Data type column as 2/9/2010, 22/10/2010 etc. Following UK Date here.
It is required to pull the data to COUNT No. of Expired. Obviously below one fails.
--AS Date AND Month stored in Single digits AND double digits , below statement
--thorws Arthmetic overflow exception
SUM(CASE WHEN CONVERT(DATETIME,ExpiryDate,103) >= (CONVERT(datetime,'2010-01-07',103)) AND
CONVERT(DATETIME,ExpiryDate,103) < dateadd(month,1,(convert(datetime,'2010-01-09', 103))) THEN 1 ELSE 0 END) AS Expired
View Complete Post