out-of-range smalldatetime value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

out-of-range smalldatetime value

Author

Topic

d3ng
Yak Posting Veteran

83 Posts

Posted - 2007-05-10 : 02:07:59

Hi experts. I would like to ask about the error message that i've encountered.

Error Message:
Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

This is the SQL Query that I executed.

SELECT Patinv.renuser, Patinv.rentime, Patinv.rendate, Items.itemdesc, Items.unit,
Items.genericname,Patitem.renqty, Patitem.itemid,Patitem.trackno, Patinv.trackno
FROM Patinv INNER JOIN Patitem
ON Patinv.trackno = Patitem.trackno INNER JOIN dbo.items Items ON Patitem.itemid = Items.itemid
WHERE Patinv.rendate+Patinv.rentime >= '01/1/2007 06:00 AM'
AND Patinv.rendate+Patinv.rentime <= '05/10/2007 06:00 AM'
AND Items.category = 'MED'

Note: Patinv.rendate - (smalldatetime datatype) - '01/1/2007'
Patinv.rentime - (Char(8) datatype)- '06:00 AM'

Then I tried to modify my Query. I created a more simplified query

select top 10 pattranno, rendate from patinv
WHERE patinv.rendate + convert(smalldatetime,patinv.rentime,108) >= '2006-08-14 23:57:00'
AND patinv.rendate + convert(smalldatetime,patinv.rentime,108) <= '2006-08-14 23:57:00'

But still it's error... Can you help with this? Badly needed.

Darren Bernabe Blanco

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-10 : 02:52:37

What is the data type for column rendate and rentime? Why are you storing date and time separately?

Check the below code to concatenate date and time:

declare @dt datetime, @tm datetime

set @dt = '2007-02-01 12:12:24'

set @tm = '12:52:31'

select @dt [date], @tm [time], dateadd(second, datediff(second, 0, @tm), dateadd(day, datediff(day, 0, @dt), 0))  [date and time]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

d3ng
Yak Posting Veteran

83 Posts

Posted - 2007-05-10 : 03:25:42

Hi Harsh thanks to your effort.

Here is the datatype:
Patinv.rendate - (smalldatetime datatype)
Patinv.rentime - (Char(8) datatype)

Im combining the rendate & rentime to get the exact value of this for ex. '2006-08-14 23:57:00'

This is the sample data stored in my table patinv
(Select top 10 rendate, rentime from patinv where rendate is not null)

Rendate Rentime
------------------ --------
2005-06-07 00:00:00 04:36 PM
2006-08-14 00:00:00 11:57 PM
2005-06-07 00:00:00 04:36 PM
2005-06-07 00:00:00 04:36 PM
2005-06-07 00:00:00 04:36 PM
2005-06-07 00:00:00 04:36 PM
2005-06-07 00:00:00 04:36 PM
2005-06-07 00:00:00 04:36 PM

Note: my query is being used in a remote views / views

What also confuses me is that I tried to execute the same query to another database of our client and then it successfully retrieved a records, because we have the repository of our clients database for testing purposes. The 2 differences between the 2 databases that I tried to used are the data of the Database A (where in the error occurs) is converted from SQL 2000 to SQL 2005 while the Database B (where in the query works) is using the SQL 2000 but both data is being stored using SQL 2005. By the way Harsh Athalye I added you to my MSN Messenger.. here's my MSN Account cerebralkink@hotmail.com

Darren Bernabe Blanco

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-10 : 03:50:27

1. Don't store date and time separately
2. Don't use character data types like CHAR or VARCHAR to store date and time data

See the below sample script to see how to combine date and time stored in different columns:

-- prepare sample data
declare @t table
(
	a smalldatetime,
	b char(8)
)

insert into @t
select '2005-06-07 00:00:00', '04:36 PM' union all
select '2006-08-14 00:00:00', '11:57 PM' union all
select '2005-06-07 00:00:00', '04:36 PM' union all
select '2005-06-07 00:00:00', '04:36 PM' union all
select '2005-06-07 00:00:00', '04:36 PM' union all
select '2005-06-07 00:00:00', '04:36 PM' union all
select '2005-06-07 00:00:00', '04:36 PM' union all
select '2005-06-07 00:00:00', '04:36 PM'

-- query to combine date and time
select a [only date], b [only time], 
dateadd(second, datediff(second, 0, cast(b as smalldatetime)), dateadd(day, datediff(day, 0, a), 0))  [date and time]
from @t

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources