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)
 Date problem in query from view		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jh_sql
Starting Member

Finland
22 Posts
Posted - 11/27/2012 :  02:56:43  Show Profile  Reply with Quote
Hello, i have problem with following query:

start_time is datetime, pid bigint.

This works:

SELECT * FROM (
SELECT start_time,pid FROM v_resource_usage_new WHERE PID=489
) AS v_resource_usage_new
ORDER BY start_time

Belowe gives: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

SELECT * FROM (
SELECT start_time,pid FROM v_resource_usage_new WHERE PID=489
) AS v_resource_usage_new
WHERE (isnull(start_time,'1.1.2000') BETWEEN '11.1.2012' AND '11.30.2012')
ORDER BY start_time

Data without the date comparison is:
start_time pid
2012-10-16 00:00:00.000 489
2012-10-16 00:00:00.000 489
2012-11-08 00:00:00.000 489
2012-11-08 00:00:00.000 489
2012-11-08 00:00:00.000 489
2012-11-08 00:00:00.000 489
2012-11-09 00:00:00.000 489
2012-11-09 00:00:00.000 489
2012-11-12 00:00:00.000 489
2012-11-12 00:00:00.000 489
2012-11-13 00:00:00.000 489
2012-11-13 00:00:00.000 489
2012-11-14 00:00:00.000 489
2012-11-14 00:00:00.000 489
2012-11-14 00:00:00.000 489
2012-11-14 00:00:00.000 489
2012-11-17 00:00:00.000 489
2012-11-17 00:00:00.000 489
2012-11-18 00:00:00.000 489
2012-11-18 00:00:00.000 489
2012-11-19 00:00:00.000 489
2012-11-19 00:00:00.000 489
2012-11-21 00:00:00.000 489
2012-11-21 00:00:00.000 489
2012-11-22 00:00:00.000 489
2012-11-22 00:00:00.000 489
2012-11-23 00:00:00.000 489
2012-11-23 00:00:00.000 489
2012-11-28 00:00:00.000 489
2012-11-28 00:00:00.000 489

Cant seem to see any problem with the data, and im extremely buzzled on this, any help?

khtan
In (Som, Ni, Yak)

Singapore
16746 Posts
Posted - 11/27/2012 :  03:11:20  Show Profile  Reply with Quote 

WHERE start_time BETWEEN '2012-11-01' AND '2012-11-30'


KH
Time is always against us
Go to Top of Page

stepson
Yak Posting Veteran

Romania
85 Posts
Posted - 11/27/2012 :  03:11:25  Show Profile  Reply with Quote
The first work because is a simply select

the second make some conversion , as the error say


you can test the field <star_time> if is a datetime type with ISDATE ( expression )


also try to change the where condition:
where start_time between '20121101' and '20121130'




Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

stepson
Yak Posting Veteran

Romania
85 Posts
Posted - 11/27/2012 :  03:13:38  Show Profile  Reply with Quote
one posibil problem is here '11.1.2012'

use '11.01.2012'

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

jh_sql
Starting Member

Finland
22 Posts
Posted - 11/27/2012 :  03:26:25  Show Profile  Reply with Quote
Thanks for fast replies.

Default date format in SQL server is m.d.y, however it displays data y-m-d.

Weirdest thing is that if i add TOP to inner query it works, like this:

SELECT * FROM (
SELECT TOP 1000000 start_time,pid FROM v_resource_usage_new WHERE PID=489
) AS v_resource_usage_new
WHERE (start_time BETWEEN '11.1.2012' AND '11.30.2012')
ORDER BY start_time

v_resource_usage_new has total 1326 rows, so TOP shouldnt even do anything to query :S:S
Edited by - jh_sql on 11/27/2012 03:27:18
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