Solved

# How can I create an indexable formula column that generates the julian integer of another datetime column?

Posted on 2004-10-04
816 Views
I want to add a formula column that converts a datetime field to it's julian int.  This can be done either by, convert(int, theDate) or dateDiff(day, 0, theDate)

Both of which are deterministic and precise according to SQL Server documentation, however, when I go to add an index on the column Sql Server bombs saying "Cannot create index because the key column 'julian' is non-deterministic or imprecise."

CREATE TABLE dbo.Test1 (
theDate datetime NULL
,julian  AS convert(int, [d], 1)
)

CREATE NONCLUSTERED INDEX IX_Test1
ON dbo.Test1 (julian)

So my question is:  "How can I create an indexable formula column that generates the julian integer of another datetime column?"
0
Question by:mjschehl
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 3

Author Comment

ID: 12219051
I noticed a typo, the index should be:

CREATE NONCLUSTERED INDEX IX_Test1
ON dbo.Test1 (julian)
0

LVL 15

Expert Comment

ID: 12219094
try this

create table dbo.Test1
(
theDate datetime null
)

create view dbo.VTest1
(
select
convert(int, [d], 1) as julian
from
dbo.Test1
)

CREATE NONCLUSTERED INDEX IX_VTest1
ON dbo.VTest1(julian)
0

LVL 15

Expert Comment

ID: 12219116
err the correct syntax is here

create table dbo.Test1
(
thedate datetime null
)

create view VTest1
as

select
convert(int, thedate, 1) as julian
from
Test1

CREATE NONCLUSTERED INDEX IX_VTest1
ON dbo.VTest1(julian)

0

LVL 15

Expert Comment

ID: 12219147
http://fox.wikis.com/wc.dll?Wiki~IndexedViews~VFP

is suck ... go there to check out indexing views
0

Author Comment

ID: 12219234
justinbillig,

There were schema binding problems with this.  If we were to get the schema binding problems resolved, would your solution allow me to index on my main Test1 table?
0

Author Comment

ID: 12219309
Just to be clear, I truly need the index on the main table.  That is the only way I believe I can eek the performance out that I need.

I only need this index for a short time to convert the data to a new db schema.   The real table already has a long record length and 300 million records.  So, I am avoiding copying the data into a new table-- or materialized view.  Also, I am avoiding adding a new column.
0

Author Comment

ID: 12220418

I came up with a work-around.

I wrote the following formula which calculates the Julian date.  The formula works correctly for dates starting at jan 1, 1900, and more importantly, Sql Server doesn't have a problem with it:

((datepart(year,[theDate]) - 1900) * 365 + floor(((datepart(year,[theDate]) - 1901) / 4)) + datepart(dayofyear,[theDate]) - 1)
0

LVL 9

Accepted Solution

miron earned 275 total points
ID: 12222425
this works:

set QUOTED_IDENTIFIER on
go
set ARITHABORT on
go
create table dt( c1 datetime , c2 as cast( floor( convert( numeric( 35, 17) , c1 , 121 ) ) as int )  )
go
create index ix__c2__dt on dt( c2 )
go
set QUOTED_IDENTIFIER off
go
set ARITHABORT off
go
0

Author Comment

ID: 12225919

Good work Miron.  I wish Microsoft's products actually worked as expected.  But until then, I guess I should be happy that sometimes there are work-a-rounds!

0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides? Â Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff lâ€¦
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages â€“ plus untold reputational damage to one of the worldâ€™s most trusted airlines. All due to a catastrophâ€¦
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
###### Suggested Courses
Course of the Month2 days, 17 hours left to enroll