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)
 pls help

Author  Topic 

Amit.Hublikar
Starting Member

3 Posts

Posted - 2013-10-29 : 02:17:11
Hi
I am new to SQL SERVER.I Am Facing the below problem .

From java servlet I am pulling the report that is to be downloaded into excel sheet .But when I am runnging the query I am not getting proper data .and getting the error as mentioned below:

Query:
"select Case_ID_,BranchCode,Site,IP_Address,Category,PDAWAPType,Status,
dateadd(s,(Down_since),'19700101 05:30:00'),dateadd(s,(Up_Since),'19700101 05:30:00'),
dateadd(s,(ETR),'19700101 04:00:00'),dateadd(s,(Create_Time),'19700101') from HPD_HelpDesk
where Create_Time >= convert(bigint,datediff(s,'19700101',convert(datetime,'10/01/2013 00:00:00')))
AND Create_Time <= convert(bigint,datediff(s,'19700101',convert(datetime,'10/29/2013 23:59:59')))
and Status >=4 and Site like 'canara'"


when i run the above query I am getting the below error at runtime :
SQLException: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Difference of two datetime columns caused overflow at runtime.

kindly pls help


visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-29 : 02:41:05
Whats the datatype of Create_Time?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Amit.Hublikar
Starting Member

3 Posts

Posted - 2013-10-29 : 04:16:14
Data Type of Create_Time is int
Go to Top of Page

Amit.Hublikar
Starting Member

3 Posts

Posted - 2013-10-29 : 05:06:35
quote:
Originally posted by visakh16

Whats the datatype of Create_Time?
Data type of create_time is int.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-29 : 05:29:46
The reason may be that Down_since,Up_Since, ETR etc date fields have some really early values (say 19000101 etc) so that datediff value in seconds is going beyond integer limit. Try using higher period like hh,minute etc and see it works first

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -