Peristed Data in calculated datetime column (nvarchar to datetime conversion)

I am having trouble getting SQL 2005 to let me setup a persisted data field.  Here is what I have (it really sucks, but I have to deal with it as is....unfortunately).

SQL Table as Follows (snippet)

Date_of_Record_Year -> varchar(2)
Date_of_Record_Month -> varchar(2)
Date_of_Record_Day -> varchar(2)
Time_Of_Record_Hours -> varchar(2)
Time_Of_Record_Minutes -> varchar(2)
Time_Of_Record_Seconds -> varchar(2)
Time_Of_Record_Seconds_100 -> varchar(2)

Example of Todays Date would be (1/15/07 @ 8:30 PM)
Date_of_Record_Year = 07
Date_of_Record_Month = 01
Date_of_Record_Day = 15
Time_Of_Record_Hours = 20
Time_Of_Record_Minutes = 30
Time_Of_Record_Seconds = 00
Time_Of_Record_Seconds_100 = 00

Obviously trying to search a database by date is difficult (Select xx BETWEEN x AND y) and very slow as I have to CAST my query to be a datetime for my queries.  What I am trying to do is create a computed column based on these values, which I have done successfully.

Computed Column Formula:
(CONVERT([datetime],((((((((([Date_of_Record_Month]+'-')+[Date_of_Record_Day])+'-')+[Date_of_Record_Year])+' ')+[Time_of_Record_Hours])+':')+[Time_of_Record_Minutes])+':')+[Time_of_Record_Seconds],(0)))

Once I have this in place, it does compute my date correctly.  I now need to be able to index this column (21 Million Rows in DB).  Every time I attempt to try to set this field up as persisted I get the following error.

Computed column 'My_Date' in table 'xxx' cannot be persisted because the column is non-deterministic.  

Any help would be greatly appreciated.




Start Free Trial