Problems with this query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Problems with this query

Author

Topic

mxd198
Starting Member

7 Posts

Posted - 2009-05-06 : 15:13:15

select rpt_cat.category,convert(datetime,rpt_cat.DateRange,101),rpt_cat.userid, rpt_cat.UserFirstName, rpt_cat.UserLastName
From
(SELECT o11.a_fldtp_desc as category,
"DateRange" = case
when o01.pg_create_date is null then convert(varchar,convert(datetime,o01.user_key_9), 101)
else convert(varchar,convert(datetime,o01.pg_create_date), 101)
end,
su.userid, su.user_first_name AS UserFirstName,
su.user_last_name AS UserLastName,
su.user_login_id AS UserLoginId
FROM optimg11 o11 INNER JOIN (optimg01 o01 LEFT JOIN sec_users su ON o01.pg_create_uid = su.userid)
ON o11.a_fldtp_id = o01.pg_folder_id
Where o11.a_fldtp_desc = 'PA') rpt_cat

Where (rpt_cat.DateRange between '01/01/2009' AND '05/06/2009')
ORDER BY rpt_cat.category,rpt_cat.DateRange;

When I run the above query, I get this error:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

If I run the inner query by itself, it works great! what am I missing? I've been working on this for awhile now. I just can't get it....

thanks for your help!

shaunc
Starting Member

28 Posts

Posted - 2009-05-06 : 15:21:39

When you run the inner query, do you get any impossible values for DateRange? Something like February 30th, for example.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-05-06 : 15:30:56

why do you want to convert the date to a varchar and then back to a datetime...try this..

select rpt_cat.category,rpt_cat.DateRange,rpt_cat.userid, rpt_cat.UserFirstName, rpt_cat.UserLastName
From
(SELECT o11.a_fldtp_desc as category,
"DateRange" = case
when o01.pg_create_date is null then convert(datetime,o01.user_key_9)
else convert(datetime,o01.pg_create_date)
end,
su.userid, su.user_first_name AS UserFirstName,
su.user_last_name AS UserLastName,
su.user_login_id AS UserLoginId
FROM optimg11 o11 INNER JOIN (optimg01 o01 LEFT JOIN sec_users su ON o01.pg_create_uid = su.userid)
ON o11.a_fldtp_id = o01.pg_folder_id
Where o11.a_fldtp_desc = 'PA') rpt_cat

Where (rpt_cat.DateRange between '01/01/2009' AND '05/06/2009')
ORDER BY rpt_cat.category,rpt_cat.DateRange;

mxd198
Starting Member

7 Posts

Posted - 2009-05-06 : 15:37:40

Well, i was at the point that I was trying anything to get it to work! I looked at the dates and I don't see anything unusual. Now I do have date like "01/01/9999" because those were used to fill in dates that were not issued.

mxd198
Starting Member

7 Posts

Posted - 2009-05-06 : 15:40:34

vijay,
Copy and pasted your sql and still received this error:

Server: Msg 242, Level 16, State 3, Line 5
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

mxd198
Starting Member

7 Posts

Posted - 2009-05-06 : 15:56:38

I have to leave for the day, i work on it tomorrow. cya tomorrow! thanks!

mxd198
Starting Member

7 Posts

Posted - 2009-05-07 : 09:53:45

Does anyone have any other advice?

thanks!

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-07 : 10:07:22

I think '01/01/9999' is valid. try this to find out the invalid ones,

select user_key_9 from optimg11 where isdate(user_key_9)<>1
select pg_create_date from optimg11 where isdate(pg_create_date)<>1

mxd198
Starting Member

7 Posts

Posted - 2009-05-07 : 10:20:39

Yes, I just tried the 1st one and got alot of errors:
02282005
01/172005
10/04/200
NA/NA/NANA
NA/NA/NANA
NULL
10/09/1007
na/na/nana
$1,000
10/16/200

So, how would I only pick the good dates?

thanks so much!!

mxd198
Starting Member

7 Posts

Posted - 2009-05-07 : 10:21:06

The other one had nulls in it.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-07 : 12:17:16

by having a filter like

select
	..
from
	..
where 
	isdate(user_key_9)=1 and isdate(pg_create_date)=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources