SQL Server, views and datetime conversions...

Question

So I have a base table that looks something like this.

SELECT [BILL_MONTH]
      ,[BILL_YEAR]
      ,[BILLED]
 FROM bill_Detail

Everything is varchar.

I created a view based on that table that converts the bill year and bill month to a datetime field. Of course, the bill_month and bill_year fields have some crap data that doesn't convert so I have the following WHERE clause in my view definition.

WHERE   ISDATE(CONVERT(varchar(4), BILL_YEAR)  + '-' + CONVERT(varchar(3), BILL_MONTH) + '-1' ) = 1

The view works like a champ when I do a plain select on it. No bad dates, everything cleaned up, etc. However, when I try and do any sort of date arithmetic (dateadd, datediff) I start getting conversion errors.

EDIT: Adding in actual view and test select statement:

Code for the view

SELECT  ID
 ,BILLED
 ,Payment_Type
 ,CONVERT(datetime, CONVERT(varchar(4), BILL_YEAR)  + '-'  + CONVERT(varchar(3), BILL_MONTH) + '-1' ) 
    AS BillDate FROM    dbo.Detail AS d WHERE   ISDATE(CONVERT(varchar(4), BILL_YEAR)  + '-' + CONVERT(varchar(3), BILL_MONTH) + '-1' ) = 1 AND  ISNULL(Payment_Code,'') = '' AND  Payment_Type in ('D','I')

Here is the select statement that blows up.

SELECT ID
  ,[BILLED]
  ,[Payment_Type]
  ,[BillDate] FROM vw_DelinquentDetail where isdate(billdate) = 1

The select statement is where the following error pops up.

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Sorry for the code formatting on the first section.

Answer 1

you didn't include the select clause of your view, but you need to make sure you're converting the BILL_MONTH] and [BILL_YEAR] columns of your base table to a single datetime column in the view.

Answer 2

Can you provide the SELECT part of the view too, i.e. how you actually convert them to datetime? I think it's quite relevant.

Answer 3

I know this isn't really answering your question, but is it feasible for you to just return the raw string value to your business code and do all the conversions and calculations there? It would be much simpler and safer.

Answer 4

make sure you cast or convert the date to a datetime in the view:

create table testdates
(BILL_MONTH  varchar(3)
,BILL_YEAR   char(4)
,BILLED char(1)
)

insert into testdates values ('01','2009','y')
insert into testdates values ('02','2009','y')
insert into testdates values ('03','2009','y')
insert into testdates values ('a','bbb','n')

create view testdates_v as
select
CONVERT(datetime,CONVERT(varchar(4), BILL_YEAR)  + '-' + CONVERT(varchar(3), BILL_MONTH) + '-1') as billdate
from testdates
WHERE   ISDATE(CONVERT(varchar(4), BILL_YEAR)  + '-' + CONVERT(varchar(3), BILL_MONTH) + '-1' ) = 1
go

now try to use the view:

select billdate from testdates_v

OUTPUT:

billdate
-----------------------
2009-01-01 00:00:00.000
2009-02-01 00:00:00.000
2009-03-01 00:00:00.000

(3 row(s) affected)


select billdate+1,dateadd(mi,45,billdate) from  testdates_v

output:

----------------------- -----------------------
2009-01-02 00:00:00.000 2009-01-01 00:45:00.000
2009-02-02 00:00:00.000 2009-02-01 00:45:00.000
2009-03-02 00:00:00.000 2009-03-01 00:45:00.000

(3 row(s) affected)

EDIT after OP's edit giving more code:

SQL SERVER is probably trying to build out the billdate column before it applies the WHERE. As a result, change your view to select billdate using a CASE, so it is NULL when not a valid date:

SELECT  ID
        ,BILLED
        ,Payment_Type
        ,CASE
             WHEN ISDATE(CONVERT(varchar(4), BILL_YEAR)  + '-' + CONVERT(varchar(3), BILL_MONTH) + '-1' ) = 1  THEN CONVERT(datetime, CONVERT(varchar(4), BILL_YEAR)  + '-'  + CONVERT(varchar(3), BILL_MONTH) + '-1' ) 
             ELSE NULL
         END AS BillDate 
    FROM    dbo.Detail AS d 
    WHERE   ISDATE(CONVERT(varchar(4), BILL_YEAR)  + '-' + CONVERT(varchar(3), BILL_MONTH) + '-1' ) = 1 AND         ISNULL(Payment_Code,'') = '' AND                Payment_Type in ('D','I')

using the above view, the error goes away.