CODEWITHSUNDEEP
sqlsql-server
BigJobbies
BigJobbies

Reputation: 4055

SQL Server - The conversion of char data type to smalldatetime

I was wondering if someone could help me.

I have created a small SQL query, but every time I run it, I get the following error

The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetype value.

The SQL I'm trying to run is as follows:

UPDATE sales 
SET dateOut = '2014-04-31 12:32:46' 
WHERE userID = '11111' AND dateOut >= '2014-04-01 12:32:46'

Any help would be greatly appreciated.

Cheers,

Upvotes: 0

Views: 8160

Answers (2)

Dan Hastings
Dan Hastings

Reputation: 3290

I had the exact same issue and couldnt figure it out. Turned out the SQL server was located in a different timezone to the machine i was running the code on. The extension methods i was using for the DateTime object were using UK date format and even though SQL Server management studio was displaying the same date format, the underlying system was not using this format.

I was able to make it work using the following.

System.DateTime currentdate = System.DateTime.Now;
string currentDateString = currentdate.Month + "/" + currentdate.Day + "/" + currentdate.Year;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270773

I have no idea why you would be getting this error. A safer way to do the conversion (because it does not relay on system date/time formatting preferences) is to use convert(). So you can try:

UPDATE sales
    SET dateOut = convert(smalldatetime, '2014-04-31 12:32:46', 120)
    WHERE userID='11111' AND
          dateOut >= convert(smalldatetime, '2014-04-01 12:32:46', 120);

EDIT:

DUH! April only has 30 days. Try doing 

UPDATE sales
    SET dateOut = convert(smalldatetime, '2014-04-30 12:32:46', 120)
    WHERE userID='11111' AND
          dateOut >= convert(smalldatetime, '2014-04-01 12:32:46', 120);

Actually, with this keen observation, your original will work with a correct date:

UPDATE sales
    SET dateOut ='2014-04-30 12:32:46'
    WHERE userID='11111' AND dateOut >= '2014-04-01 12:32:46';

Upvotes: 2

Related Questions