• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1291
  • Last Modified:

Whats the difference in SQL 2008 between datetime and smalldatetime?

I have a statement that now throws an error since I migrated to SQL 2008 from MS SQL 2000.

SQL Statement reads

SELECT PhoneNumber, Message, DateSent,TimeSent, UserName, Originator, MessageStatus FROM messagelog WHERE DateSent >= '21/09/2010' AND DateSent <='21/09/2010' AND MessageStatus LIKE '%%' AND UserName = '' ORDER BY DateSent Desc ;

Error reads

Microsoft OLE DB Provider for SQL Server error '80040e07'

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

When I try to chage the data type to Datetime in the database , MSSQL 2008 says the table would have to be droped and recreated and it cannot do that.

What can I do to solve this?
0
souldj
Asked:
souldj
  • 5
  • 3
  • 2
  • +1
6 Solutions
 
Kevin CrossChief Technology OfficerCommented:
souldj,

Your issue is likely not dealing with the difference between SMALLDATETIME and DATETIME data types.  The issue is probably that the new server's language is not set to British Enlgish and so the '21/09/2010' or 'DD/MM/YYYY' date format is not being recognized.  As 'MM/DD/YYYY', 21 would be out of range as a month value.

Here is information on the difference in the types if you are still interested:
It is all about the range and precision of the date values -
http://msdn.microsoft.com/en-us/library/aa258277(SQL.80).aspx

Regards,

Kevin
0
 
avirupsCommented:
A similar question was answered already in Experts Exchange: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22963429.html

The main difference between these two datatypes is in the amount of space they occupy.
Datetime occupies eight bytes and smalldatetime only four.
The difference in size is due to a difference in precision.
The precision of smalldatetime is one minute, and it covers dates from January 1, 1900 , through June 6, 2079 , which is usually more than enough. The precision of datetime is 3.33 ms, and it covers dates from January 1, 1753 , to December 31, 9999 .
0
 
avirupsCommented:
Details information about the date time handling in SQL Server 2008 can be found here:

http://www.databasejournal.com/features/mssql/article.php/3718086/New-datetime-datatypes-in-SQL-Server-2008.htm

Scroll down on the page to find the information specific to smalldatetime and datetime. Otherwise it is a very good read to know what all you can do.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
kovilpattiBaluCommented:
Datetime and Smalldatetime
======================

Datetime
========
Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds.

Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900.
The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

Smalldatetime
=============
Date and time data from January 1, 1900, through June 6, 2079,with accuracy to the minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999  seconds or higher are rounded up to the nearest minute.

The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number
of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900,  through June 6, 2079, with accuracy to the minute.
0
 
souldjAuthor Commented:
Thanks for all the info, but it still does not tell me what exactly I need to do to rectify the problem.

i specificall asked

"When I try to chage the data type to Datetime in the database , MSSQL 2008 says the table would have to be droped and recreated and it cannot do that.

What can I do to solve this? "

Please guide me :-(

Thanks
0
 
avirupsCommented:
Try this:
SELECT * FROM mytable
WHERE ISDATE([mycolumn])=0

This will return you all the records
where value of [mycolumn] can't be converted to date.
And you should fix those records before again trying to alter the column.
0
 
avirupsCommented:
Otherwise, it is simple enough to
1. create another table which is empty with the same structure.
2. While creating this table choose the datetime datatype for the column.
3. Then copy the data from original table to the new table.
4. rename the original table to something else for backup purposes.
5. rename the new table to the name of the original table.

This way you can alter the column data type where a datatype conversion is not possible.
0
 
souldjAuthor Commented:
avirups, Thanks, I think your second option is more likely to be the solution as the database is over 300,000 lines.

What is the SQL code I could use to copy ALL Data from one table to another before changing the table name
0
 
Kevin CrossChief Technology OfficerCommented:
Did you ever read my comment: http:#a33721551 ?

Your issue is likely nothing to do with the column being smalldatetime.  The error you are getting is not in the data in your smalldatetime column but the parameter that you are passing it which it has to convert from varchar to smalldatetime.  Please review my comment and will save you the heartache of changing your data for no reason.

Now given the differences in date range and precision for datetime over smalldatetime, if you do decide to update the column, you can simply do this with T-SQL:

ALTER TABLE MessageLog
ALTER COLUMN DateSent DATETIME;

Now since this is likely a field defaulted to the current time, you will probably get a failure message -- but that is good news as it will give you the constraint name -- drop the constraint listed (you can and probably should go into the table definition and ensure that it is doing what I think it is which is default value to GETDATE()) -- after the alter statement is done, then go back and create the default value through designer or T-SQL.

Likely drop constraint expression:
ALTER TABLE MessageLog DROP CONSTRAINT DF__MessageLog__DateSent__{some identifier};

Kevin
0
 
Kevin CrossChief Technology OfficerCommented:
If you copy all your data to another table, you may run into other issues of losing more important constraints and foreign key relationships.  If you are going to take the approach of copy and drop, then simply :

(1) Add a new column that represents DateSentDt DateTime.
(2) Run this T-SQL: Update MessageLog Set DateSentDt = DateSent;
(2) Drop column DateSent.

Again, this won't solve your issue with the varchar error, but it will allow you to change the data type of the column.

Good luck!
0
 
avirupsCommented:
select * into new_table from table1;
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now