Solved

Whats the difference in SQL 2008 between datetime and smalldatetime?

Posted on 2010-09-20
11
1,197 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 7

Assisted Solution

by:kovilpattiBalu
kovilpattiBalu earned 83 total points
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 8

Assisted Solution

by:avirups
avirups earned 167 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
select * into new_table from table1;
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Backup & Restore 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.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now