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)
 Date problem in query from view

Author  Topic 

jh_sql
Starting Member

24 Posts

Posted - 2012-11-27 : 02:56:43
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)

17689 Posts

Posted - 2012-11-27 : 03:11:20
[code]
WHERE start_time BETWEEN '2012-11-01' AND '2012-11-30'
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-11-27 : 03:11:25
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
Aged Yak Warrior

545 Posts

Posted - 2012-11-27 : 03:13:38
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

24 Posts

Posted - 2012-11-27 : 03:26:25
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
Go to Top of Page
   

- Advertisement -