Solved

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

Posted on 2004-10-04
9
789 Views
Last Modified: 2012-06-27
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
Comment
Question by:mjschehl
  • 5
  • 3
9 Comments
 

Author Comment

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

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

Expert Comment

by:justinbillig
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

by:justinbillig
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

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


is suck ... go there to check out indexing views
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:mjschehl
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

by:mjschehl
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

by:mjschehl
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

by:
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

by:mjschehl
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now