The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value

0
votes

I've a SQL query which looks like :

select LAST_UPDATED from Employees WHERE (CAST(LAST_UPDATED AS smalldatetime)) > (CAST('@updateTime' AS smalldatetime))

command.Parameters.AddWithValue("updateTime", updatedTime);

I went through several of the related questions asked here and also this page but none could solve my issue.

I also tried the ISO-8601 format for dates (YYYYMMDD) which also doesn't seem to work here.

The data type of LAST_UPDATED column in SQL server is DATE.

The problem is like this works well in development machine and in SQL management studio but throws the following error in Test server:

The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value

SQL management studio query:

select LAST_UPDATED from Employees WHERE (CAST(LAST_UPDATED AS smalldatetime)) > (CAST('2014-08-10 ' AS smalldatetime))

It would be very helpful if someone can put some light on this.

3
c#sqlsql-server
Are you writing the variable name as literal instead of an actual variable? – EzLo
You're trying to convert the string '@updateTime' to a smalldatetime - not the value of the @updateTime variable. – Damien_The_Unbeliever
Instead of AddWithValue I recommend use Parameters.Add and defining the datatype of your input parameter in your C# code. – Larnu
Can you include how you are assigning the variable updatedTime value in C#? Might be some date dependent OS settings thats formatting the date that makes SQL Server fail when converting. – EzLo
@EzLo : Thanks to you, issue was in the way updatedTime was being assigned! – m_beta

3 Answers

1
votes

Ahh!Finally the issue is solved. I was assigning string updatedTime like this:

string updatedTime = strUpdateTime.Substring(0, 11);
string strUpdateTime = GetLastUpdate()

So what my code looked like is:

string strUpdateTime = GetLastUpdate()
string updatedTime = strUpdateTime.Substring(0, 11);

select LAST_UPDATED from Employees WHERE (CAST(LAST_UPDATED AS smalldatetime)) > (CAST('@updateTime' AS smalldatetime))

command.Parameters.AddWithValue("updateTime", updatedTime);

And while searching an answer for post I came accross Mike Clark's answer and noticed that I've also used Substring(). So instead of passing updatedTime I passed strUpdateTime

command.Parameters.AddWithValue("updateTime", strUpdateTime);

If one needs to change the format of DateTime it can be done in several ways .

Thanks to everyone who has contributed to this post.

0
votes

Check out these examples:

DECLARE @updateTime VARCHAR(20) = '2020-01-01'

SELECT CAST('@updateTime' AS smalldatetime)

Msg 295, Level 16, State 3, Line 3 Conversion failed when converting character string to smalldatetime data type.

Now if we remove the single quotes, we will actually access the content of the variable and not the hard-coded string '@updateTime':

DECLARE @updateTime VARCHAR(20) = '2020-01-01'

SELECT CAST(@updateTime AS smalldatetime)

Result:

2020-01-01 00:00:00

Single quotes are used to delimit literal values. You can access a variable's content with their name (starting with the @), without any quotes.

If the problem is actually the LAST_UPDATED contents, then replace your forced CAST for a TRY_PARSE, which won't fail if the LAST_UPDATED content isn't convertible.

select LAST_UPDATED from Employees 
WHERE TRY_PARSE(LAST_UPDATED AS smalldatetime) > TRY_PARSE(@updateTime AS smalldatetime)
0
votes

Like I said in the comments, don't pass an nvarchar, pass a date parameter, and then you don't need to cast it. So in your c# instead do:

command.Parameters.Add("updateTime",SqldbType.DateTime).Value = updatedTime

Then your SQL can be as simple as:

SELECT LAST_UPDATED
FROM Employees
WHERE LAST_UPDATED > @updateTime;

Assuming, of course, that LAST_UPDATED is a (small)datetime; but then, why wouldn't it be if it's being compared as a Date?