I have a list of julian dates that I need to keep in order ex. 362, 363, 364, 365, 001, 002, 003. My query starts with getting the last julian date processed and each date after that. Right now it will max my lowest date out at 365 and I can't get the records that follow it. The same set of data also has a date field with the year attached but it doesn't seem to be helpful since those records won't be gathered until the rollover is corrected. Here is my simplified query:
select JulianDate, RecordDate
from table
where JulianField > @LowestJulianDate
and RecordDate between GetDate() and DateAdd(day, 6, GetDate())
Sample date:
| JulianDate | RecordDate |
|---|---|
| 362 | 2020-12-28 |
| 363 | 2020-12-29 |
| 364 | 2020-12-30 |
| 365 | 2020-12-31 |
| 001 | 2021-01-01 |
| 002 | 2021-01-02 |
| 003 | 2021-01-03 |
Desired output:
| JulianDate |
|---|
| 362 |
| 363 |
| 364 |
| 365 |
| 001 |
| 002 |
| 003 |
So if you'll imagine we start on day 362, our @LowestJulianDate is 362, and our record date range is today and the next 6 days, completing that list of julian dates.
How can I get the dates to go in order and resolve in a rollover?
You cannot by just using the "JulianDate" which is actually the DayOfYear value. You would need to also store the year that it refers to either separately or as part of the "JulianDate" value. For example, instead of "362" you need "2021362".