I checked your profile and saw that you are in the UK. If your sql server is set to use the dateformat dmy then that explains your issue. Without using the 'T' instead of the space in the datetime string, Sql Server won't recognize it as ISO8601 format.
Try this:
select count(*)
from dbo.profile
where [created] between convert(datetime,'2014-11-01T00:00:00.000')
and convert(datetime,'2014-11-30T23:59:59.997');
Querying using dates and/or datetimes can be tricky, to make sure you are getting what you are looking for I recommend reading:
edit: to clarify the out of range value in your error message would be from interpreting the month as 30 and the day as 11.
Since you are not shared the sample data of the Gradd19 value, I suspect the date value is in the yyyy-dd-mm format, so when trying to convert (month value more than 12) it cause the out-of-range value error.
For sample, if the date format in yyyy-mm-dd, it will execute properly:
DECLARE @FromDate DATETIME;
DECLARE @Gradd19 AS VARCHAR (20) = '2016-11-15';
SELECT @FromDate = CONVERT(VARCHAR, @Gradd19, 105);
SELECT @FromDate;
Incase if it is in yyyy-dd-mm it will throw out-of-range value error
DECLARE @FromDate1 DATETIME;
DECLARE @Gradd191 AS VARCHAR (20) = '2016-15-11';
SELECT @FromDate1 = CONVERT(VARCHAR, @Gradd191, 105);
SELECT @FromDate1;
So in that case you need do one more conversion to skip the issue, like the below:
DECLARE @FromDate2 DATETIME;
DECLARE @Gradd192 AS VARCHAR (20) = '2016-15-11';
SELECT @FromDate2 = CONVERT(VARCHAR(19), CONVERT(DATETIME, @Gradd192, 105), 120)
SELECT @FromDate2;
So instead of
CONVERT(VARCHAR, Gradd19, 105) AS ExpiryDate
you need to use
CONVERT(VARCHAR(19), CONVERT(DATETIME, Gradd19, 105), 120) AS ExpiryDate
Update: As you mentioned in the comments the Gradd19 having the values of 20 and 60, it is difficult to filter those non-valid data. But based on the LEN() function you may avoid the non-valid data, then using the CONVERT you can get the VARCAHR field as DATETIME value.
Based on your sample data the valid date's minimum length is 10 characters, so you can apply the condition in the WHERE clause.
Sample execution:
DECLARE @GradedDETAILS TABLE (Gradd19 VARCHAR (30));
INSERT INTO @GradedDETAILS (Gradd19) VALUES
('Sep 16 2016 12:00AM'),
('Sep 16 2016 12:00AM'),
('Sep 16 2016 12:00AM'),
('Sep 19 2016 12:00AM'),
('Sep 19 2016 12:00AM'),
('Sep 16 2016 12:00AM'),
('20'),
('60'),
('20-09-2016'),
('20-09-2016');
SELECT CONVERT(VARCHAR(19), CONVERT(DATETIME, Gradd19, 105), 120)
FROM @GradedDETAILS
WHERE LEN(Gradd19) >= 10
So in your actual query also you need to add the LEN(Gradd19) >= 10 condition and in the SELECT use this to convert as DATETIME by CONVERT(VARCHAR(19), CONVERT(DATETIME, Gradd19, 105), 120)
Best Answer
In your first example, you compare a datetime to a string. The format for that string is YYYY-MM-DD, meaning that the login's language will determine what is to be interpreted and month vs day. You seems to be in the UK, I assume a British login. Check out for instance sp_helplanguage you'll see how the "separated" datetime format is interpreted for various languages.
The "separated" format is, btw, not to be confused with the ISO 8601 format, which is YYYY-MM-DDTHH:MM:SS (the letter T is what makes the difference).
When you cast the column to the date type, you no longer compare the string to datetime, you compare it to the more modern date type. This will always interpret the "separated" format as ymd, and is in other words not dependent on the login's language.
If you were using the more modern datetime2, btw, you wouldn't have the problem in the first place, since it is handled as date.
See my datetime article for further elaboration: https://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes .