I have faced the same problem a week ago. The problem is with the time zone setting. Specify in other formats like mm/dd/yyyy (usually works).
Specifying the date as 30/12/2013 resulted in the error for me. However, specifying it as mm/dd/yyyy format worked.
If you need to convert your input the you can try looking into the CONVERT method.
Syntax is
CONVERT(VARCHAR,@your_date_Value,103)
CONVERT(VARCHAR, '12/30/2013', 103)
The finishing 103 is the datetime format.
Refer this link for conversion formats and further reading. https://www.w3schools.com/sql/func_sqlserver_convert.asp
Answer from Mahe on Stack OverflowI have faced the same problem a week ago. The problem is with the time zone setting. Specify in other formats like mm/dd/yyyy (usually works).
Specifying the date as 30/12/2013 resulted in the error for me. However, specifying it as mm/dd/yyyy format worked.
If you need to convert your input the you can try looking into the CONVERT method.
Syntax is
CONVERT(VARCHAR,@your_date_Value,103)
CONVERT(VARCHAR, '12/30/2013', 103)
The finishing 103 is the datetime format.
Refer this link for conversion formats and further reading. https://www.w3schools.com/sql/func_sqlserver_convert.asp
I ran into this issue due to a silly mistake. Make sure the date actually exists!
For example:
September 31, 2015 does not exist.
EXEC dbo.SearchByDateRange @Start = '20150901' , @End = '20150931'
So this fails with the message:
Error converting data type varchar to datetime.
To fix it, input a valid date:
EXEC dbo.SearchByDateRange @Start = '20150901' , @End = '20150930'
And it executes just fine.
Error when conversion of a varchar data type to a datetime
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. UWP
Varchar to date
Out of range data conversion error
That example values are convertable. You can analyse your extisting 263 K records on not-convertable by using TRY_CONVERT function
SELECT *
FROM yourTable
WHERE TRY_CONVERT(date, columnField, 103) IS NULL
datetime data type resulted in an out-of-range value.
datetime has a valid range from 1753-01-01 til 9999-12-31. If the date is before 1753, you get this error, see
https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver16
Please post some sample values you try to convert.
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:
- Aaron Bertrand's Bad habits to kick : mis-handling date / range queries
- Robyn Page's SQL Server DATE/TIME Workbench
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.
I do not understand why the data is being converted from varchar to datetime when 'Created' is set to datetime
The literals you are providing for comparison to the Created column are strings. To compare those literals with the datetime column, SQL Server attempts to convert the strings to datetime types, according to the rules of data type precedence. Without explicit information about the format of the strings, SQL Server follows its convoluted rules for interpreting strings as datetimes.
In my view, the neatest way to avoid these types of issues is to be explicit about types. SQL Server provides the CAST and CONVERT functions for this purpose. When working with strings and date/time types, CONVERT is to be preferred because it provides a style parameter to explicitly define the string format.
The question uses strings in ODBC canonical (with milliseconds) format (style 121). Being explicit about the data type and string style results in the following:
SELECT COUNT(*)
FROM dbo.profile
WHERE [Created] BETWEEN
CONVERT(datetime, '2014-11-01 00:00:00.000', 121)
AND
CONVERT(datetime, '2014-11-30 23:59:59.997', 121);
That said, there are good reasons (as Aaron points out in his answer) to use a half-open range instead of BETWEEN (I use style 120 below just for variety):
SELECT COUNT(*)
FROM dbo.profile
WHERE
[Created] >= CONVERT(datetime, '2014-11-01 00:00:00', 120)
AND [Created] < CONVERT(datetime, '2014-12-01 00:00:00', 120);
Being explicit about types is a very good habit to get into, particularly when dealing with dates and times.