Date problem in query from view

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]

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources