convert date issue in view

i have created a view to a table (90,000+ records) that i want to use to filter the table by the current year and month. the code looks like this:

CREATE VIEW [billy.bhuj].[bo current month]

AS SELECT [dbo].[bo].[recnum], [dbo].[bo].[queue],
[dbo].[bo].[queue_name], [dbo].[bo].[node],
[dbo].[bo].[interval], [dbo].[bo].[tot_calls],
[dbo].[bo].[calls_less_20_sec], [dbo].[bo].[calls_more_20_sec],
[dbo].[bo].[calls_abandon], [dbo].[bo].[abandon_before_20_sec],
[dbo].[bo].[abandon_after_20_sec],
[dbo].[bo].[queue_date],
month (convert (datetime,[dbo].[bo].[queue_date], 103)) as QDate,
year (convert (datetime,[dbo].[bo].[queue_date], 103)) as QDate1,
convert (datetime,[dbo].[bo].[queue_date], 103) as QDate2,
year (convert (datetime,(getdate()), 104)) as year1,
[dbo].[bo].[region], [dbo].[bo].[queue_type],
[dbo].[bo].[month],
[dbo].[bo].[unit],
[dbo].[bo].[service], [dbo].[bo].[reportable], [dbo].[bo].[source_dest],
[dbo].[bo].[file_name]

FROM [dbo].[bo]

Where (year (convert (datetime,[dbo].[bo].[queue_date], 104)))
= (year (convert (datetime,(getdate()), 104)))

the syntax checks fine, and without the "where" clause , i get all the original data returned no problem, so the "month", "year", and "convert" functions work fine. however, when i try to filter the data with the "where" clause above, i get about 15-20 lines of data returned and an error message referring to "Arithmetic overflow...". on their own in the "select" area the statements do what they should, but in the "where" statement they don't. sorry, i'm a big time newbie in sql, so any help would be appreciated.

hi,

are your trying to do something like a parameterized view

well if you are you should be creating stored procedure

rather than a view

regards,

joey

|||

sorry, my bad...

i re checked the results without the "where" clause, and i was getting an error code further down the result list! same Arithmetic Overflow" error! i removed all the "convert" lines and it runs okay.

bo.queue_date is a nvarchar. i am using "convert" to change it to a date field. i do not own the table where bo.queue_date originates.

so how do i convert from a nvarchar field to a date field, and then filter out the dates i don't want in the "where" clause? should i be using "cast" instead of "convert"? or could this be a result of improper data in the bo.queue_date of the original table?

i am not trying to pass a parameter, just filter the main table down to a more manageable size by filtering by current month and year. if a stored proc makes more sense to do this though, i could try that instead...

|||

How do you know if the values in queue_date can be successfully converted to datetime? Is it always in one particular format? Why is that column nvarchar instead of datetime or smalldatetime? You could do check like below and then convert to prevent the conversion error:

select ...

, year(t.q_dt)

, month(t.q_dt)

from (

select ...

, case isdate(bo.queue_date) when 1 then convert(datetime, bo.queue_date, 104) end as q_dt

from bo

) as t

where year(t.q_dt) = year(CURRENT_TIMESTAMP)

But I don't think this is the problem. You talked about arithmetic overflow error which is different. Is bo a table or view? If bo is a view then you need to check the SELECT statement of the bo to see if there are any expressions that can result in arithmetic overflow. Also, please post the exact error message when asking for help.