July 16, 2013 at 6:43 am
Im using this syntax to get a value but it gives me a error in visual studio:
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value
Syntax im using in visual studio:
select h.branch_n, type_n, count(incident_id) as x
from incident as a
join sectn_dept as c
on a.sectn_dept_id = c.sectn_dept_id
join inc_cat
on a.inc_cat_id = inc_cat.inc_cat_id
join cat_proces_mngt
on a.inc_cat_id = cat_proces_mngt.inc_cat_id
join time
on a.date_logged1 = time.pk_date
join sectn as g
on c.sectn_id = g.sectn_id
join branch as h
on g.branch_id = h.branch_id
where exists (
select f.branch_n, count(*) as y from incident as b
join sectn_dept as d
on b.sectn_dept_id = d.sectn_dept_id
join inc_cat
on b.inc_cat_id = inc_cat.inc_cat_id
join cat_proces_mngt
on b.inc_cat_id = cat_proces_mngt.inc_cat_id
join sectn as e
on d.sectn_id = e.sectn_id
join branch as f
on e.branch_id = f.branch_id
where c.sectn_n = d.sectn_n
and date_logged1 >= @week and date_logged1 < dateadd(week, 1, @week1)
and location_id = 2 and type_id in (1,2,3)
and week >= @week
group by f.branch_n
having count(*) > 10 and count(*) <= 30)
and date_logged1 >= @week and date_logged1 < dateadd(week, 1, @week1)
and location_id = 2 and type_id in (1,2,3)
and week >= @week
group by h.branch_n, type_n
order by h.branch_n, type_n desc
WHEN I'm running in Microsoft server management studio it works fine and i get results like this;
Branch_n -- type_n -- x
Cook ST Change6
Cook Question7
Cook Incident5
BVST Change7
BVQuestion5
BVIncident12
GST Change7
GQuestion3
July 16, 2013 at 6:48 am
Check the language for each of the logins as this determines conversion of character dates.
Are you passing the date parameters as character? If so is there a specific reason?
Far away is close at hand in the images of elsewhere.
Anon.
July 16, 2013 at 7:00 am
David Burrows (7/16/2013)
Check the language for each of the logins as this determines conversion of character dates.Are you passing the date parameters as character? If so is there a specific reason?
Like the qoute says from DAVID BURROWS; Far away is close at hand in the images of elsewhere.
I just see now that the week1 as character but must be date/time. I just need that BOOST haha just 9 hours SQL TYPING!
July 16, 2013 at 7:20 am
And if at all possible do yourself a favor and change the column to datetime. When you store datetime information in another datatype you lose the ability to do calculations, you lose the ability to validate that the value is in fact a datetime. Of course the last time I checked there is no need for any unicode characters in ANY date format. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply