• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 423
  • Last Modified:

Where clasue with julian date or convert julian date to date time

I have a user that need a report that pulls transactions between a certain time, say 0600 - 1600. The problem I have is that the date/time in the table is julian date. How can I select records just within those hours? Do I need to convert the julian date to date/time?

For example, here is one of the julian dates in the table: 2456020.5005

I would want to run a query similar to:

select *
from table1
where juliandate between 0600 and 1600

Thanks!
0
wiggy353
Asked:
wiggy353
  • 2
1 Solution
 
wdosanjosCommented:
2456020.5005 does not seem to be a Julian Date.  Julian Dates are in the format YYDDD or YYYYJJJ, where YY or YYYY is the year and JJJ is the number of days since the beginning of the year.
0
 
nishant joshiTechnology Development ConsultantCommented:
below is a function to find date enter your date and find using between within hours,day,...


ALTER FUNCTION [dbo].[fnConvertJulianToDate]
(
 @JD DECIMAL(18,5)
)
RETURNS DATETIME
AS
BEGIN
 
 DECLARE @j INT
 DECLARE @g INT
 DECLARE @dg INT
 DECLARE @c INT
 DECLARE @dc INT
 DECLARE @b INT
 DECLARE @db INT
 DECLARE @a2 INT
 DECLARE @da INT
 DECLARE @y2 INT
 DECLARE @m2 INT
 DECLARE @d INT
 DECLARE @year INT
 DECLARE @month INT
 DECLARE @day INT
 DECLARE @hr INT
 DECLARE @min INT
 DECLARE @sec INT
 DECLARE @frac DECIMAL(18,5)
 DECLARE @f DECIMAL(18,2)
 DECLARE @finalDateTime DATETIME
 
 
 SET @frac =  @JD - FLOOR(@JD) + 0.5
 
 IF(@frac >= 1.0)
  BEGIN
   SET @frac = @frac - 1.0
   SET @JD = @JD + 1.0
  END
 
 SET @j = FLOOR(@JD) + 32044
 SET @g = @j / 146097
 SET @dg = @j % 146097
 SET @c = (@dg / 36524 + 1) * 3 / 4
 SET @dc = @dg - @c * 36524
 SET @b = @dc / 1461
 SET @db = @dc % 1461
 SET @a2 = (@db / 365 + 1) * 3 / 4
 SET @da = @db - @a2 * 365
 SET @y2 = @g * 400 + @c * 100 + @b * 4 + @a2
 SET @m2 = (@da * 5 + 308) / 153 - 2
 SET @d = @da - (@m2 + 4) * 153 / 5 + 122
 SET @year = @y2 - 4800 + (@m2 + 2) / 12
 SET @month = (@m2 + 2) % 12 + 1
 SET @day = @d + 1.5
 
 
 SET @hr  = FLOOR(@frac * 24.00)
 SET @min  = FLOOR((@frac*24.00 - @hr)*60.00)
 SET @f  = ((@frac*24.00 - @hr)*60.00 - @min)*60.00
 SET @sec = FLOOR(@f)
 SET @f = @f - @sec
 
 IF( @f > 0.5 )
  BEGIN
   SET @sec = @sec + 1
  END
 
 SET @finalDateTime = CONVERT(VARCHAR(20), CAST(@month AS VARCHAR(2))
    + '/'
    + CAST(@day AS VARCHAR(2))
    + '/'
    + CAST(@year AS VARCHAR(4))
    + ' '
    + CAST(@hr AS VARCHAR(2))
    + ':'
    + CAST(@min AS VARCHAR(2))
    + ':'
    + CAST(@sec AS VARCHAR(2)))
 
 RETURN @finalDateTime
 
END

Open in new window

0
 
wdosanjosCommented:
Sorry I take that back.  It seems there are two types of Julian Dates and the one you are referring to is the Astronomy one.  If that's the case, the following SQL should do the trick:
select *
from table1
where ((juliandate - round(juliandate, 0, 1)) * 24) between 6.0 and 16.0

Open in new window


Julian day
http://en.wikipedia.org/wiki/Julian_day
0
 
wiggy353Author Commented:
Perfect, thanks!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now