convert date issue in view - 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

SQL Server 2000 Forums

Transact-SQL (2000)

convert date issue in view

Author

Topic

billy_bhuj
Starting Member

4 Posts

Posted - 2006-09-20 : 12:57:14

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.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-20 : 13:24:07

what datatype is bo.queue_date?

are you sure you get all the data fine without the where clause? Maybe there is an error reported waaaay down at the end of the result list?

I've seen a lot of people use the "style" argument when converting a value TO datetime. What is the benifit of this? Isn't the "style" argument ignored when converting to anything besides character based types?

Be One with the Optimizer
TG

billy_bhuj
Starting Member

4 Posts

Posted - 2006-09-20 : 13:41:13

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.

tg, you were right about getting an error code further down the result list! same Arithmetic Overflow" error! i removed all the "convert" lines and it runs okay.

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?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-20 : 13:48:20

If there is anyway you can, you really should redesign your model so that dates are stored as datetime.

The problem is that you have queue_date values that are representing invalid dates.
One thing you can do is locate the invalid entries with something like:

select * from bo where isDate(queue_date) = 0

when those are corrected your code should work. If you want to ignore the invalid dates for now you could replace your bo table with a derived table:


select	<yourColList>
from	(
	select <BO_ColList>
	from 	bo
	where	isDate(queue_date) = 1
	) bo
where	<yourWhere>

Be One with the Optimizer
TG

billy_bhuj
Starting Member

4 Posts

Posted - 2006-09-20 : 13:52:24

thanks tg, i will look at those two ideas. i was afraid that there may be invalid data in this field. i have no control over this table, however. long story...

billy_bhuj
Starting Member

4 Posts

Posted - 2006-09-20 : 14:04:29

unfortunately,the code

select * from bo where isDate(queue_date) = 0

on the original table will return all entries because they are all nvarchar! the whole point is to convert them to dates and then filter via a where statement.

as well, the line

where isDate(queue_date) = 1

will it not return only those entries wich are dates? that would be none, as again, this field is nvarchar?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-20 : 14:57:01

select isDate(N'2006-12-31')
--this returns 1

The fact that it is nvarchar is not the issue, it is probably the format. Post an example of the date values:

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources