SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 pls help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Amit.Hublikar
Starting Member

India
3 Posts

Posted - 10/29/2013 :  02:17:11  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 10/29/2013 :  02:41:05  Show Profile  Reply with Quote
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

India
3 Posts

Posted - 10/29/2013 :  04:16:14  Show Profile  Reply with Quote
Data Type of Create_Time is int
Go to Top of Page

Amit.Hublikar
Starting Member

India
3 Posts

Posted - 10/29/2013 :  05:06:35  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 10/29/2013 :  05:29:46  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000