Solved

Whats the difference in SQL 2008 between datetime and smalldatetime?

Posted on 2010-09-20
11
1,208 Views
Last Modified: 2012-05-10
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
Comment
Question by:souldj
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 33721551
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
 
LVL 8

Expert Comment

by:avirups
ID: 33722254
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
 
LVL 8

Expert Comment

by:avirups
ID: 33722263
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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 7

Assisted Solution

by:kovilpattiBalu
kovilpattiBalu earned 83 total points
ID: 33723468
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
 
LVL 1

Author Comment

by:souldj
ID: 33723665
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
 
LVL 8

Assisted Solution

by:avirups
avirups earned 167 total points
ID: 33723679
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
 
LVL 8

Expert Comment

by:avirups
ID: 33723703
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
 
LVL 1

Author Comment

by:souldj
ID: 33724393
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
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 250 total points
ID: 33724480
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
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 250 total points
ID: 33724506
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
 
LVL 8

Assisted Solution

by:avirups
avirups earned 167 total points
ID: 33784916
select * into new_table from table1;
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question