Complex query

[jasonburi]

An existing DB I am working with was created using a field for each part of a date instead of one date field. With a date field it is easy enough to write a query to get everything greater than a certain date. Is there a good way to write a query against each column (year, julian_day, hour, minute, second) to return values after a certain date. Assuming I want everything after date X The only thing I can think of in pseudo code is

If yearDB > yearX OR
yearDB = yearX and julianDayDB > julianDayX OR
yearDB = yearX and julianDayDB = julianDayX and
hourDB > hourX OR
etc.

Written in SQL as a WHERE clause with the above AND/OR groups.

 

[MarkButler]

How about something like the following:

WHERE CONVERT(DATETIME, CAST(table.monthportion AS CHAR(2)) + '/' + CAST(table.dayportion AS CHAR(2)) + '/' + CAST(table.yearportion AS CHAR(4))) > GETDATE()

 

[tektipdjango]

another way

declare @minDate as int
set @minDate = year(X)*512 + datepart(dy,X)
...
where yearDB*512 + dayDB > @minDate

I make a multiply by 512 and not 366 as the computer can do a left move fo the number(512=2 power 9) of nine bits, which is a lot quicker then the multiply

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[jasonburi]

Thank you both for the suggestions.

MarkButler:
I tried an extended version of this that included all of the time portions of a date from enterprise manager on my test server (MS SQL Server), and it worked like a champ. Then I went to try it in the production system (Sybase) and from isql it won't let me type the whole thing in. I guess I'll just have to bite the bullet and code it to see how it works. I ran a profiler session while I ran this query then my original idea. Your suggestion required fewer reads.

tektipdjango:
I guess if I wanted to try this as a full test I have to find the power of 2 nearest to 31,622,400 (366 d/y * 24h/d * 60 m/h * 60 s/m)? (2^25) That takes care of the years . . . then repeat for DD, HH and MM. Am I building a lot of error into this by modifying what you initally posted?